Database Keys in SQL and Performing Operations on Employee Database
Learn about primary keys, foreign keys, and unique keys in SQL databases. Explore how to define keys in tables and perform various SQL operations on an employee database, such as displaying employees, salaries, job IDs, and calculating PF.
Download Presentation
Please find below an Image/Link to download the presentation.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author. If you encounter any issues during the download, it is possible that the publisher has removed the file from their server.
You are allowed to download the files provided on this website for personal or commercial use, subject to the condition that they are used lawfully. All files are the property of their respective owners.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author.
E N D
Presentation Transcript
CGS 3066: Web Programming and Design CGS 3066: Web Programming and Design Fall 2019 Fall 2019 More on SQL
Primary Key A primary key is a special relational database table column (or combination of columns) designated to uniquely identify all table records. A primary key s main features are: It must contain a unique value for each row of data. It cannot contain null values. A primary key is either an existing table column or a column that is specifically generated by the database according to a defined sequence. (techopedia.com)
Primary Key Primary keys can be defined when creating a table: CREATE TABLE table_name ( id_col INT PRIMARY KEY, col2 CHARACTER VARYING(20), ... ) Or can be defined using alter table query by adding constraint: ALTER TABLE <table identifier> ADD [ CONSTRAINT <constraint identifier> ] PRIMARY KEY ( <column expression> {, <column expression>}... )
Foreign Key A foreign key is a column or group of columns in a relational database table that provides a link between data in two tables. It acts as a cross- reference between tables because it references the primary key of another table, thereby establishing a link between them. (techopedia.com)
Unique Key A unique key is a set of one or more than one fields/columns of a table that uniquely identify a record in a database table. Unique keys can be defined when creating a table: CREATE TABLE table_name ( id_col INT PRIMARY KEY, col2 CHARACTER VARYING(20), ... identifier> ( <column expression> {, <column eUNIQUE INDEX <constraint xpression>}... ) ) Or can be defined using alter table query by adding constraint: ALTER TABLE <table identifier> ADD [ CONSTRAINT <constraint identifier> ] UNIQUE INDEX( <column expression> {, <column expression>}... ) ALTERTABLE fruits ADDCONSTRAINT uq_1 UNIQUEINDEX (`name`, color )
Perform SQL Operations on Employee Database https://www.w3resource.com/mysql-exercises/basic-simple- exercises/index.php 1. Display all employees 2. Display salaries of all employees 3. Display the unique job_ids for the employees 4. Display IDs and names (first_name, last_name) using alias full_name 5. Select names (first_name, last_name), salary, PF of all the employees (PF is calculated as 15% of salary)
