Download
1 / 12
120 likes | 223 Views
Tutorial. SQL Data Definition Language. q.1. Creating a Table for the following Staff, make sure the Dno be included as one of the department numbers in the department table, sex should be restricted to F or M, Salary should be no more than 50000.00.
E N D
Tutorial SQL Data Definition Language
q.1. Creating a Table for the following Staff, make sure the Dno be included as one of the department numbers in the department table, sex should be restricted to F or M, Salary should be no more than 50000.00 Staff ( Fname, Lname, SSN, DOB, Address, Sex, Salary, Dno) CREATE DOMAIN DepartmentNo AS INT CHECK (VALUE IN (SELECT Dnumber FROM department)); CREATE DOMAIN SexType AS CHAR CHECK (VALUE IN (‘M’, ‘F’)); CREATE DOMAIN SalaryType AS DECIMAL(8,2) CHECK (VALUE IN (0, 50000.00); CREATE TABLE staff (Fname VARCHAR(15) NOT NULL, Lname VARCHAR(15) NOT NULL, SSN CHAR(9) NOT NULL, DOB DATE, Address VARCHAR(30), Sex SexType DEFAULT ‘F’, Salary SalaryType, Dno DepartmentNo NOT NULL, PRIMARY KEY (SSN), FOREIGN KEY (Dno) REFERENCES DEPARTMENT(Dnumber);
Creating a Table for the department, using Dname as Primary key DEPARTMENT( Dname, Dnumber) Solution: CREATE TABLE department ( Dname VARCHAR(15) NOT NULL, Dnumber INT NOT NULL, PRIMARY KEY (Dname),) Lilac Safadi SQL (DDL)
q. 3 : Add an attribute for keeping track of jobs of staff in the company schema Add an attribute for the staff degree Solution: ALTER TABLE company.staff ADD job VARCHAR(12); ADD Degree VARCHAR(12); Lilac Safadi SQL (DDL)
q.4: Remove the address attribute from the staff table, remove the sex attribute, add yearsofExpeirence, • Solution ALTER TABLE company.staff DROP address CASCASE; DROP Sex CASCADE; ADD yearsofExpeirence INT; Lilac Safadi SQL (DDL)
Changing a Table Definition q.5 Change the staff table by removing the default of ‘Assistant’ for the position column and setting the default for the sex column to female Solution: ALTER TABLE staff ALTER position DROP DEFAULT; ALTER TABLE staff ALTER sex SET DEFAULT ‘F’; Lilac Safadi SQL (DDL)
q.7. Change the staff table by adding a constraint that the staff salary doesnot exceed 50000 ALTER TABLE Staff ADD CONSTRAINT StaffSalary <= 50000.00; q.8. Change the PropertyForRent table by removing the constraint that the staff are not allowed more than 100 properties at a time ALTER TABLE PropertyForRent DROP CONSTRAINT StaffNotHandlingTooMuch CASCADE; Lilac Safadi SQL (DDL)
q.9. Change the staff table by making name a primary key other than Id ALTER TABLE Staff DROP CONSTRAINT IDISKey CASCADE ADD CONSTRAINT NameIsKey PRIMARY KEY (name); Lilac Safadi SQL (DDL)
q. 10 Removing the staff table only if it does not have data • Solution: • DROP TABLE Staff RESTRICT; Lilac Safadi SQL (DDL)
q. 12. Creating an Index using staff last name CRETAE UNIQUE INDEX StaffInd ON staff (Lname); Lilac Safadi SQL (DDL)
q.13. Create a view for managers at branch B003 can see only the details for staff who work in their branch office, and write an SQL statemant of how to use it at branch B003 Solution: CREATE VIEW Maneger3Staff AS SELECT * FROM staff WHERE branchNo = ‘B003’; SELECT * FROM Manager3Staff; Lilac Safadi SQL (DDL)
q.13. Create a view for staff details at branch B003 that excludes salary information, and write a statement to see information of staff Solution: CREATE VIEW Staff3 AS SELECT StaffNo, Fname, Lname, position, sex FROM staff WHERE branchNo = ‘B003’; CREATE VIEW Staff3 AS SELECT StaffNo, Fname, Lname, position, sex FROM Manager3Staff; Lilac Safadi SQL (DDL)
