Scribd
Upload
13 views2 pages

SQL Database Creation and Management Guide

The document outlines the creation of a database schema with tables for Department, Employee, Project, Dependent, Work_on, and Dept_location, including their relationships and constraints. It includes SQL commands for creating tables, altering them to add foreign keys, and inserting sample data into the Employee and Department tables. Additionally, it demonstrates various SQL queries for retrieving and manipulating date-related information from the Employee table.

Uploaded by

gadisakarorsa
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
13 views2 pages

SQL Database Creation and Management Guide

The document outlines the creation of a database schema with tables for Department, Employee, Project, Dependent, Work_on, and Dept_location, including their relationships and constraints. It includes SQL commands for creating tables, altering them to add foreign keys, and inserting sample data into the Employee and Department tables. Additionally, it demonstrates various SQL queries for retrieving and manipulating date-related information from the Employee table.

Uploaded by

gadisakarorsa
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

Create database skill

use skill

create table Department(depno varchar(20) primary key,Depname char(20),MGRSSN


varchar(20),start_date date)
Alter table Department add constraint fk_dp foreign key(MGRSSN) references
Employee(ESSN)

create table Employee(ESSN varchar(20) primary key,Fname varchar(20),Lname


varchar(20),sex varchar(6),
address char(20),salary float,Bdate date,depno varchar(20) foreign key(depno)
references Department(depno)
on delete cascade on update cascade,constraint salary_range check(salary>=1500
and salary<=20000))
Alter table Employee add constraint fk_Sup foreign key(sup_id) references
Employee(ESSN)
Alter table Employee add sup_id varchar(20)

create table project(pno varchar(20) primary key,name char(20),location


varchar(30),
depno varchar(20),constraint fk_dpro foreign key(depno) references
department(depno))

create table dependent(Fname char(20),sex varchar(6),Bdate date,Relationship


varchar(20),
ESSN varchar(20)foreign key(ESSN) references Employee(ESSN) on delete cascade
on update cascade,primary key(Fname,ESSN))

create table Work_on(ESSN varchar(20) foreign key references Employee(ESSN) on


delete cascade on update cascade,pno varchar(20)
foreign key(pno) references project(pno),hours int,primary key(ESSN,pno))

create table dept_location(depno varchar(20) foreign key references


department(depno),
location varchar(20) primary key(depno,location))

select * from Employee


insert into Employee values('ESSN 01','Solomon','Abebe','m','Adama',5000,'12-
5-1985',null)
insert into Employee values('ESSN 02','Abel','Tadesse','m','Addis
Abeba',2500,'01-28-1950',null)
insert into Employee values('ESSN
03','Mikyas','Derebe','m','Jigijiga',11000,'07-12-1979',null)
insert into Employee values('ESSN 04','Helen','Hagos','f','Mekele',12280,'08-
30-1981',null)
insert into Employee values('ESSN
05','Biniam','Tafesse','m','Hawassa',8000,'02-25-1978',null)
insert into Employee values('ESSN
06','Samrawit','Alemu','f','Bahirdar',15000,'11-13-1969',null)
insert into Employee values('ESSN 07','Gizaw','Nida','m','Axum',10000,'11-11-
1980',null)

select GETDATE() 'current date'


select Fname +' '+ Lname 'full name' ,DATEPART(YY
,Bdate)'year',DATEPART(mm,Bdate)'month',DATEPART (dd,Bdate)'date'
from Employee
select DATEADD (DAY,45,Bdate)'birth date' from Employee where ESSN ='ESSN 01'
select dateadd (month,1,Bdate) from employee where ESSN='ESSN 01'
select DATEADD (year,1,Bdate) from Employee where ESSN ='ESSN 01'
select DATEDIFF(YEAR ,'1980-11-11','1985-12-05') 'year diff'
Select Fname from Employee where sex='m' and DATEDIFF(YEAR,Bdate,GETDATE
())>50

insert into Department values('D01','ICT','ESSN 01','2008-05-15')


insert into Department values('D02','Marketing','ESSN 02','2009-07-29')
insert into Department values('D03','Finance','ESSN 06','2011-08-07')
insert into Department values('D04','Administration','ESSN 04','1995-05-12')
insert into Department values('D05','Researching','ESSN 05','1995-05-12')

You might also like