CE-301 DATABASE MANAGEMENT SYSTEMS 2020-CE-095
“Lab # 4”
Object: Apply creating and altering tables commands in SQL
Lab Tasks
SQL PRIMARY KEY Constraint on CREATE TABLE
To DROP a PRIMARY KEY Constraint
To drop a PRIMARY KEY constraint, use the following SQL:
SQL FOREIGN KEY Constraint on CREATE TABLE
To DROP a FOREIGN KEY Constraint
To drop a FOREIGN KEY constraint, use the following SQL:
1
CE-301 DATABASE MANAGEMENT SYSTEMS 2020-CE-095
Adding Data with the INSERT Statement
We can add a new record or row into a table by using the INSERT statement.
SYNTAX
INSERT [INTO] <table> [(column_list)]
VALUES (data_values)
Where:
Table is the name of the table
Column is the name of the column in the table to populate Value is the
corresponding value for the column
INTO is optional and columns list is also optional and may be omitted if you list
values in the default order of the columns in the table.
Type the following query.
This result could also have been achieved by using this query if the column order
is default:
2
CE-301 DATABASE MANAGEMENT SYSTEMS 2020-CE-095
INSERTING NULL VALUES IN ROWS
Implicit Method: Omit the Column Name From the Column List
Explicit Method: Specify the NULL keyword or empty string (‘ ‘) in the values list.
INSERTING SPECIAL VALUES USING SQL FUNCTIONS
Considering the following query:
Here an employee names Shahnawaz has been added to the employee table, it
uses the GETDATE() function for current date and time. To confirm the addition of
new employee data into the employee’s table, type this query into SSMS:
3
CE-301 DATABASE MANAGEMENT SYSTEMS 2020-CE-095
INSERTING SPECIFIC DATE VALUES
To insert a row or record with some specific date, consider the following query:
INSERTING VALUES BY USING SUBSTITUTION VARIABLE
COPYING ROWS FROM ANOTHER TABLE
Updating/Changing
Data With The UPDATE Statement
4
CE-301 DATABASE MANAGEMENT SYSTEMS 2020-CE-095
Updating Rows in a Table
The update statement can modify rows specified by the WHERE clause condition.
Omitting the WHERE will result in updating all the records in the table.
a. Consider the following query: Specific row(s) can be updated while using the WHERE
clause.
This query updates the department number information in the employee table from 30 to 10.
i. Consider the following query: All rows in the table will be updated, if we omit the WHERE
clause.
Updating with Multiple Columns Subquery:
5
CE-301 DATABASE MANAGEMENT SYSTEMS 2020-CE-095
Updating Rows Based on another Table
You can use subqueries to update records of a table. Consider the following
example:
This changes the department number of all employees in the temp table with that
of the employee number 104 in the Employee table and vice versa his job title.
Removing Data With The DELETE Statement
You can delete records of table by the DELETE statement.
Syntax
DELETE FROM table
WHERE condition
Here FROM keyword is optional. Always remember that all the rows of the table
will be deleted if not mentioning the WHERE clause.
Deleting Rows from a Table
a. Consider the following query: Specific row(s) can be deleted while using the WHERE clause.
b. Consider the following query: All rows in the table will be deleted, if we omit the WHERE
clause.
6
CE-301 DATABASE MANAGEMENT SYSTEMS 2020-CE-095
Deleting Rows Based on Another Table
You can use subqueries for deleting rows from a table based on values of some
other table. Consider the following example:
