LAB # 3&4
OBJECTIVE
Apply creating and altering tables commands in SQL
THEORY
Creating a Table
When you started this section, you looked at the standard CREATE syntax of:
CREATE <object type> <object name>
And then you moved on to a more specific start (indeed, it’s the first line of the
statement that creates the table) and created a table called Customers
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.
1. Type the following query.
insert into department(dno, dname,dloc) values
(50,'Development','Karachi')
This result could also have been achieved by using this query if the column order
is default:
insert into department values (50,'Development','Karachi')
INSERTING NULL VALUES IN ROWS
Implicit Method: Omit the Column Name From the Column List
insert into department(dno,dname) values(60,'MIS')
Explicit Method: Specify the NULL keyword or empty string (‘ ‘) in the values
insert into department values(70,'Finance',NULL)
list.
COPYING ROWS FROM ANOTHER TABLE
You can use the INSERT statement to add rows to a table whereas the values come
from existing tables. We do not use values clause, instead use a subquery (which we
may discuss later). You should match the number of columns in the INSERT clause to
those in the subquery.
Syntax
INSERT INTO table(column_list)
Subquery
Where: table is the table name where insert statement
is sought for
Column_list name of the columns in the table to be populated
Subquery subquery that returns values into the table
Consider the following query:
insert into bonus(ename,job,sal,comm)
select ename,job,sal,comm
from employee
where job='Manager'
Here Bonus table has been populated by a SELECT statement from Employee
table using the INSERT statement. Check the Bonus table by using this query.
select * from bonus
Updating/Changing Data With The UPDATE Statement
You can update an existing record or row in a table by using the UPDATE statement.
Syntax
UPDATE <table name>
SET <column> = <value>
[WHERE condition
Where: Table is the name of the table
Column is the name of the column(s) in the table to updated
Value is the corresponding value for the column
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.
update employee set dno=10 where eno=259
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.
update temp set Dno=20
Temp table has the same data as of the Employee table. You will be provided this
in the lab.
Updating with Multiple Columns Subquery
Multiple column sub-query can be used to be implemented in the SET clause to
update a row.
Syntax
UPDATE table
SET (column, column,) =
(SELECT column, column, …
FROM table
WHERE condition
Consider the following query:
update employee set job=(Select job from employee
Where eo=231), dno=(Select dno FROM employee
Where eno=231) where eno=760
Updating Rows Based on another Table
You can use subqueries to update records of a table. Consider the following example:
update temp set dno=(select dno from employee
where eno=104), job=(select job from employee
where eno=104)
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.
delete from department where dname='development'
b. Consider the following query: All rows in the table will be deleted, if we
omit the WHERE clause.
delete from dept
Dept table will be provided to you in lab.
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:
delete from temp
where dno=(select dno from department where dname='Research')
AND Operator
OR Operator
Where Clause
It is used to extract only those records that fulfill a specified criterion.
How to insert date in column
Constraints and their usage
