Study with the several resources on Docsity
Earn points by helping other students or get them with a premium plan
Prepare for your exams
Study with the several resources on Docsity
Earn points to download
Earn points by helping other students or get them with a premium plan
this helps in database management system
Typology: Cheat Sheet
1 / 5
This page cannot be seen from the preview
Don't miss anything!
department_id int PRIMARY KEY, name nvarchar( 50 ), Manager int, Manager_start_date smalldatetime ); CREATE TABLE EMPLOYEE ( SSN int PRIMARY KEY, Bdate smalldatetime, Fname nvarchar( 20 ), Minit nvarchar( 1 ), Lname nvarchar( 30 ), Address nvarchar( 100 ), Salary smallmoney, Sex bit, Department int, Supervisor int ); CREATE TABLE PROJECT ( project_id int PRIMARY KEY, name nvarchar( 50 ), location nvarchar( 50 ), controlling_department int ); CREATE TABLE DEPENDENT ( Relationship nvarchar( 30 ), Birth_date smalldatetime, Sex bit, Employee int ); INSERT INTO DEPARTMENT (department_id, name, Manager, Manager_start_date) VALUES ( 1 , 'Home Decor', 101 , '2025-01-15'), ( 2 , 'Smart Gadgets', 102 , '2025-02-01'), ( 3 , 'Logistics', 103 , '2025-03-01'); INSERT INTO EMPLOYEE (SSN, Bdate, Fname, Minit, Lname, Address, Salary, Sex, Department, Supervisor) VALUES ( 101 , '1990-05-12', 'John', 'D', 'Doe', '123 Maple St, Lahore', 55000 , 1 , 1 , NULL), ( 102 , '1992-08-22', 'Sara', 'A', 'Khan', '456 Oak Ave, Karachi', 62000 , 0 , 2 , 101 ), ( 103 , '1988-11-30', 'Mike', 'S', 'Ross', '789 Pine Rd, Islamabad', 48000 , 1 , 3 , 101 ); INSERT INTO PROJECT (project_id, name, location, controlling_department) VALUES ( 501 , 'E-commerce Expansion', 'Lahore Head Office', 2 ),
project_id int PRIMARY KEY, name nvarchar( 50 ), location nvarchar( 50 ), controlling_department int ); CREATE TABLE DEPENDENT ( Relationship nvarchar( 30 ), Birth_date smalldatetime, Sex bit, Employee int ); INSERT INTO DEPARTMENT (department_id, name, Manager, Manager_start_date) VALUES ( 1 , 'Home Decor', 101 , '2025-01-15'), ( 2 , 'Smart Gadgets', 102 , '2025-02-01'), ( 3 , 'Logistics', 103 , '2025-03-01'); INSERT INTO EMPLOYEE (SSN, Bdate, Fname, Minit, Lname, Address, Salary, Sex, Department, Supervisor) VALUES ( 101 , '1990-05-12', 'John', 'D', 'Doe', '123 Maple St, Lahore', 55000 , 1 , 1 , NULL), ( 102 , '1992-08-22', 'Sara', 'A', 'Khan', '456 Oak Ave, Karachi', 62000 , 0 , 2 , 101 ), ( 103 , '1988-11-30', 'Mike', 'S', 'Ross', '789 Pine Rd, Islamabad', 48000 , 1 , 3 , 101 ); INSERT INTO PROJECT (project_id, name, location, controlling_department) VALUES ( 501 , 'E-commerce Expansion', 'Lahore Head Office', 2 ), ( 502 , 'Global Sourcing', 'Remote/Global', 1 ), ( 503 , 'Warehouse Automation', 'Karachi Hub', 3 ); INSERT INTO DEPENDENT (Relationship, Birth_date, Sex, Employee) VALUES ('Daughter', '2018-06-10', 0 , 101 ), ('Spouse', '1993-01-20', 1 , 102 ), ('Son', '2020-03-15', 1 , 101 ); ALTER TABLE DEPENDENT ADD DependentName nvarchar( 50 ); ALTER TABLE EMPLOYEE ALTER COLUMN Supervisor varchar( 50 ); CREATE TABLE Stakeholders ( Id int PRIMARY KEY, Name nvarchar( 50 ), ContractType nvarchar( 50 ) ); ALTER TABLE PROJECT ALTER COLUMN location varchar( 70 ); EXEC sp_rename 'EMPLOYEE', 'emp'; DELETE FROM Stakeholders; DROP TABLE Stakeholders; SELECT * FROM emp; SELECT * FROM DEPARTMENT; SELECT * FROM PROJECT; SELECT * FROM DEPENDENT;
