use master ;
Create Database Company;
use company;
create table Employee (
Fname varchar(15) not null,
Minit char, ----if i do not write not null then it is by default will be
written
Lname varchar(15) not null,
ssn char(9) primary key,
bdate date,
address varchar(30),
gender char,
salary decimal(10,2),
super_ssn char(9),
Dno int not null,
);
--------------------------------------------
-- use company;
-- create table Employee (
-- Fname varchar(15) not null,
-- Minit char, ----if i do not write not null then it is by default will be
written
-- Lname varchar(15) not null,
-- ssn char(9) PK_ssn primary key,
-- bdate date,
-- address varchar(30),
-- gender char,
-- salary decimal(10,2),
-- super_ssn char(9),
-- Dno int not null,
-- -- constraint PK_ssn primary key (ssn,lname) -- composite primary key
intiliazation
-- );
-- -------------------------------------------------
-- use company;
-- create table Employee (
-- Fname varchar(15) not null,
-- Minit char, ----if i do not write not null then it is by default will be
written
-- Lname varchar(15) not null,
-- ssn char(9) ,
-- bdate date,
-- address varchar(30),
-- gender char,
-- salary decimal(10,2) default 3000,
-- super_ssn char(9),
-- Dno int not null,
-- --primary key (ssn,lname),
-- constraint PK_ssn primary key (ssn) ,
-- check (gender IN ('M' , 'F')),
-- );
-- ----------------------
-- use company;
-- create table WORKS_ON(
-- Essn char(9) not null,
-- Pno char(9) not null,
-- Hours Decimal(4,2),
--constraint prime_keys primary key (Essn,Pno)
--);
------ use company;
-- create table DEPARTMENT(
-- Dname VARCHAR(15) not null,
-- Dnumber int not null,
-- Mgr_ssn char(9) not null,
-- Mgr_start_date date,
-- primary key( Dnumber),
-- unique (Dname), ---------------------1
-- foreign key (Mgr_ssn) REFERENCES Employee(ssn)---------
--);
----------------------------
--create table DEPARTMENT(
--Dname VARCHAR(15) not null unique, -------------------2
--Dnumber int not null ,
--Mgr_ssn char(9) not null,
--Mgr_start_date date,
--primary key( Dnumber),
---------------foreign key (Mgr_ssn) REFERENCES Employee(ssn)
-------------------
--create table DEPARTMENT(
--Dname VARCHAR(15) not null constraint UQdname unique , ------------3
--Dnumber int not null,
--Mgr_ssn char(9) not null,
--Mgr_start_date date,
--primary key( Dnumber),
----------------foreign key (Mgr_ssn) REFERENCES Employee(ssn)
------------------------------
create table DEPARTMENT(
Dname VARCHAR(15) not null,
Dnumber int not null,
Mgr_ssn char(9) not null,
Mgr_start_date date default '01-01-2024'
primary key( Dnumber),
constraint UQdname unique ( Dname ) -------4
-----------foreign key (Mgr_ssn) REFERENCES Employee(ssn)
);
alter table DEPARTMENT
Drop [UQdname];
alter table DEPARTMENT
add constraint UK_department unique (Dname);
alter table DEPARTMENT
add constraint CK_dnumber check (dnumber IN (1,2,3));
alter table DEPARTMENT
drop [CK_dnumber];
alter table DEPARTMENT
add constraint FK_mgr_ssn foreign key (mgr_ssn) references Employee(ssn);
alter table DEPARTMENT
DROP [FK_mgr_ssn];
select * from customer where salary != 4000
select dnumber, dname from DEPARTMENT where Mgr_ssn = '222'
update DEPARTMENT set city = 'Tripoli';
update DEPARTMENT set city = 'marj' where dnumber = 3;
update DEPARTMENT set city = 'benghazi' where city = 'Tripoli';
update customer set address = 'al-hadaaq' where id = 1490;
select * from customer where address = 'al-hadaaq';
update DEPARTMENT set Dname = 'marketing' where Dname = 'mathmatics';
select * from DEPARTMENT where Mgr_ssn = 222
use Company;
select * from customer;
delete from customer;
delete from customer where salary <4000;
