Shaheed Zulfikar Ali Bhutto Institute of Science & Technology
COMPUTER SCIENCE DEPARTMENT
Database Systems
(Lab 07)
Lab Task # 07
Submitted To: Ms. Aunsia Khan
__________________________________________________________________________
Student Name:
__________________________________________________________________________
Reg. Number:
___________________________________________________________________________
Database Systems (Lab) BSSE-4 SZABIST-ISB
Shaheed Zulfikar Ali Bhutto Institute of Science & Technology
COMPUTER SCIENCE DEPARTMENT
1. Write a SQL statement to change the email column of
employees table with 'not available' for all employees.
UPDATE employees
SET email='NOT AVAILABLE';
2. Write a SQL statement to change the email and
commission_pct column of employees table with 'not available'
and 0.10 for all employees.
UPDATE employees
SET email='not available', commission_pct='0.10';
3. Write a SQL statement to change the email and
commission_pct column of employees table with 'not available'
and 0.10 for those employees whose department_id is 110.
UPDATE employees
SET email='not available', commission_pct='0.10'
WHERE department_id=110;
4. Write a SQL statement to change the email column of
employees table with 'not available' for those employees whose
department_id is 80 and gets a commission is less than .20%
UPDATE employees
SET email='not available'
WHERE department_id=80 AND commission < 0.20;
Database Systems (Lab) BSSE-4 SZABIST-ISB
Shaheed Zulfikar Ali Bhutto Institute of Science & Technology
COMPUTER SCIENCE DEPARTMENT
5. Write a SQL statement to change salary of employee to 8000
whose ID is 105, if the existing salary is less than 5000.
UPDATE employees
SET salary='8000'
WHERE employee_id=105 AND salary < 5000;
6. Write a SQL statement to change job ID of employee which ID
is 118, to SH_CLERK if the employee belongs to department,
which ID is 30 and the existing job ID does not start with SH.
UPDATE employees
SET job_id='SH_CLERK'
WHERE employee_id=118 AND department_id = 30;
Alter Table
1. Write a SQL statement to rename the table countries to
country_new.
ALTER TABLE countries
RENAME country_new;
2. Write a SQL statement to add a column region_id to the
table locations.
ALTER TABLE locations
ADD region_id INT;
Database Systems (Lab) BSSE-4 SZABIST-ISB
Shaheed Zulfikar Ali Bhutto Institute of Science & Technology
COMPUTER SCIENCE DEPARTMENT
3. Write a SQL statement to add a columns ID as the first
column of the table locations.
ALTER TABLE locations
ADD ID INT FIRST;
4. Write a SQL statement to add a column region_id after
state_province to the table locations.
ALTER TABLE locations
ADD region_id INT
AFTER state_province;
5. Write a SQL statement change the data type of the
column country_id to integer in the table locations.
ALTER TABLE locations
MODIFY country_id INT;
6. Write a SQL statement to drop the column city from the
table locations
ALTER TABLE locations
DROP city;
7. Write a SQL statement to change the name of the column
state_province to state, keeping the data type and size
same.
Database Systems (Lab) BSSE-4 SZABIST-ISB
Shaheed Zulfikar Ali Bhutto Institute of Science & Technology
COMPUTER SCIENCE DEPARTMENT
ALTER TABLE locations
DROP state_province,
ADD state varchar(25)
AFTER city;
8. Write a SQL statement to add a primary key for the
columns location_id in the locations table.
ALTER TABLE locations
ADD PRIMARY KEY(location_id);
9. Write a SQL statement to add a primary key for a
combination of columns location_id and country_id.
ALTER TABLE locations
ADD PRIMARY KEY(location_id,country_id);
10. Write a SQL statement to drop the existing primary
from the table locations on a combination of columns
location_id and country_id.
ALTER TABLE locations
DROP PRIMARY KEY;
Database Systems (Lab) BSSE-4 SZABIST-ISB
