Download Database example for coding and more Exercises Database Programming in PDF only on Docsity!
CIS 340 – Database Systems – LAB Works
Staff_db Database Example
Commands: Create Database, Show Databases, Connect, Create Table, Show Tables, Desc
Example (1):
Create a new database with the name Staff_db , this database contains two tables
Branch and Staff , where Branch and Staff schemas are as shown below:
Branch Staff branchNo Street City StaffNo sName Position Sex DOB Salary branchNo
Solution:
Create Database Staff_db; Show Databases; to show all the databases in MySQL Connect Staff_db; to connect to Staff_db database Show Tables; empty Create Table Branch ( branchNo char(7) Primary key, Street varchar(70), City varchar(30) ); Show Tables; Branch Desc Branch; to show the columns description of Branch table Create Table Staff ( StaffNo char(7) Primary key, sName varchar(50) not null, Position varchar(20), Sex char, DOB date, Salary double(7,2), branchNo char(7), Foreign key(branchNo) references Branch(branchNo) ON Delete Set Null ON Update Cascade ); Show Tables; Branch Staff Desc Staff; to show the columns description of Staff table
Command: Drop Table
Examples (2):
(1) Create a new table named Test in the Staff_db database. The Test table schema is as
follows:
Test TestNo TestName Solution: Show Databases; Connect Staff_db; Show Tables; Branch Staff Create Table Test ( TestNo int, TestName varchar(30), Primary key(TestNo)); Show tables; Branch Staff Test Desc Test;
(2) Remove (delete/drop) Test table from Staff_db database.
Solution:
Drop Table Test; to drop (remove) Test table from the database Show Tables; Branch Staff
Command: Insert
Examples (4):
(1) Insert the following rows into Branch table:
- (‘B005’,’22 Deer Rd’,’London’)
- (‘B00 7 ’,’16 Argyll St’,’Aberdeen’)
- (‘B00 3 ’,’163 Main St’,’Glasgow’)
- (‘B00 4 ’,’32 Manse Rd’,’Bristol’)
- (‘B00 2 ’,’56 Clover Dr’,’London’)
Solution:
Select * from Branch; to show all rows and columns of Branch table Insert into Branch values (‘B005’,’22 Deer Rd’,’London’); Insert into Branch values (‘B00 7 ’,’16 Argyll St’,’Aberdeen’); Insert into Branch values (‘B00 3 ’,’163 Main St’,’Glasgow’); Insert into Branch values (‘B00 4 ’,’32 Manse Rd’,’Bristol’); Insert into Branch values (‘B00 2 ’,’56 Clover Dr’,’London’); Select * from Branch;
(2) Insert the following rows into Staff table:
- (‘SL21’,’John’,’Manager’,’M’,’1970-10-1’,30000,’B005’)
- (‘SG37’,’Ann’,’Assistant’,’F’,’1995-11-10’, 12 000,’B00 3 ’)
- (‘SG14’,’David’,’Supervisor’,’M’,’1982-3-24’,18000,’B003’)
- (‘SA9’,’Mary’,’Assistant’,’F’,’1990-2-19’, 9 000,’B00 7 ’)
- (‘SG5’,’Susan’,’Manager’,’F’,’1972-6-3’, 24 000,’B003’)
- (‘SL41’,’Julie’,’Assistant’,’F’,’1997-6-13’, 9 000,’B00 5 ’)
Solution:
Select * from Staff; to show all columns and rows of Staff table Insert into Staff values (‘SL21’,’John’,’Manager’,’M’,’1970-10-1’,30000,’B005’); Insert into Staff values (‘SG37’,’Ann’,’Assistant’,’F’,’1995-11-10’,12000,’B003’)); Insert into Staff values (‘SG14’,’David’,’Supervisor’,’M’,’1982-3-24’,18000,’B003’); Insert into Staff values (‘SA9’,’Mary’,’Assistant’,’F’,’1990-2-19’,9000,’B007’); Insert into Staff values (‘SG5’,’Susan’,’Manager’,’F’,’1972-6-3’,24000,’B003’); Insert into Staff values (‘SL41’,’Julie’,’Assistant’,’F’,’1997-6-13’,9000,’B005’); Select * from Staff;
Command: Select
Examples (5):
Branch Staff branchNo Street City StaffNo sName Position Sex DOB Salary branchNo B005 22 Deer Rd London SL21 John Manager M 1970 - 10 - 1 30000 B B007 16 Argyll St Aberdeen SG37 Ann Assistant F 1995 - 11 - 10 12000 B B003 163 Main St Glasgow SG14 David Supervisor M 1982 - 3 - 24 18000 B B004 32 Manse Rd Bristol SA9 Mary Assistant F 1990 - 2 - 19 9000 B B002 56 Clover Dr London SG5 Susan Manager F 1972 - 6 - 3 24000 B SL41 Julie Assistant F 1997 - 6 - 13 9000 B
(1) Retrieve the data of all branches.
Select * from Branch;
(2) Retrieve branches’ numbers and cities.
Select branchNo, City to show only branchNo and City columns From Branch;
(3) Retrieve branches’ numbers and streets for ‘London’ city.
Select branchNo, Street to show specific columns From Branch Where City = ’London’ ; where (condition): to show specific rows
(4) Retrieve staff names, positions and salaries.
Select sName, Position, Salary From Staff;
(5) Retrieve staff numbers, names and date of birth for all female staff members.
Select staffNo, sName, DOB From Staff Where Sex = ‘F’;
Command: Update
Examples (6):
Branch Staff branchNo Street City StaffNo sName Position Sex DOB Salary branchNo B005 22 Deer Rd London SL21 John Manager M 1970 - 10 - 1 30000 B B007 16 Argyll St Aberdeen SG37 Ann Assistant F 1995 - 11 - 10 12000 B B003 163 Main St Glasgow SG14 David Supervisor M 1982 - 3 - 24 18000 B B004 32 Manse Rd Bristol SA9 Mary Assistant F 1990 - 2 - 19 9000 B B002 56 Clover Dr London SG5 Susan Manager F 1972 - 6 - 3 24000 B SL41 Julie Assistant F 1997 - 6 - 13 9000 B
(1) Modify (change/edit/update) the city for branch number ‘B004’ to be ‘Manchester’.
Update Branch Set City = ‘Manchester’ the column(s) to be updated Where branchNo = ‘B004’; where (condition) to update specific row(s) Select * from Branch; to show the table rows after updating
(2) Modify the streets for each branch in ‘London’ city to be ‘555 Deer Rd’.
Update Branch Set Street = ’555 Deer Rd’ Where City = ‘London’; Select * from Branch;
(3) Modify the salaries for each staff in ‘Manager’ position to be 35000.
Update Staff Set Salary = 35000 Where Position = ‘Manager’; Select * from Staff;
(4) Increase the salary for each staff in ‘Assistant’ position by 3000 more.
Update Staff Set Salary = Salary + 3000 Where Position = ‘Assistant’; Select * from Staff;
Command: Delete
Examples (7):
Branch Staff branchNo Street City StaffNo sName Position Sex DOB Salary branchNo B005 22 Deer Rd London SL21 John Manager M 1970 - 10 - 1 30000 B B007 16 Argyll St Aberdeen SG37 Ann Assistant F 1995 - 11 - 10 12000 B B003 163 Main St Glasgow SG14 David Supervisor M 1982 - 3 - 24 18000 B B004 32 Manse Rd Bristol SA9 Mary Assistant F 1990 - 2 - 19 9000 B B002 56 Clover Dr London SG5 Susan Manager F 1972 - 6 - 3 24000 B SL41 Julie Assistant F 1997 - 6 - 13 9000 B
(1) Remove (delete) all branches in ‘Bristol’ city.
Delete from Branch Where City = ‘Bristol’; to delete specific row(s) Select * from Branch;
(2) Remove all female staff members whose position is ‘Assistant’.
Delete from Staff Where Sex = ‘F’ and Position = ‘Assistant’; Select * from Branch;
