SQL Simple Database

CREATE DATABASE DATA
USE DATA

CREATE TABLE DEPT
(
DEPTNO int identity(1,1) primary key,
DNAME varchar(500) unique NOT NULL,
)

CREATE TABLE LOC
(
LNO int primary key,
DEPTID int foreign key references DEPT(DEPTNO) NOT NULL,
LNAME varchar(500) NOT NULL,
)

CREATE TABLE EMPLOY
(
EMPID int identity(1,1) primary key,
DID int foreign key references DEPT(DEPTNO) NULL,
LID int foreign key references LOC(LNO) NOT NULL,
ENAME varchar(500) NOT NULL,
)

CREATE TABLE ACCOUNTS
(
DEPT int foreign key references DEPT(DEPTNO) NOT NULL,
SAL numeric NOT NULL,
INCREMENT int default (1500)
)

INSERT INTO DEPT(DNAME)
VALUES
('ADMIN'),('MARKETING'),('SRO'),('EVALUATION'),('FACULTY'),('CLERK'),('ACCOUNTS')

INSERT INTO LOC (LNO,DEPTID,LNAME)
VALUES
(24,1,'KARACHI'),(25,2,'KARACHI'),(26,5,'KARACHI'),(27,7,'KARACHI'),
(33,3,'ISLAMABAD'),(34,4,'ISLAMABAD'),(35,5,'ISLAMABAD'),(36,6,'ISLAMABAD'),(37,7,'ISLAMABAD'),
(41,2,'FAISALABAD'),(42,3,'FAISALABAD'),(43,6,'FAISALABAD'),
(65,4,'LAHORE'),(66,3,'LAHORE'),(67,5,'LAHORE'),(68,7,'LAHORE'),
(87,4,'HYDERABAD'),(88,5,'HYDERABAD')

INSERT INTO EMPLOY(LID,DID,ENAME)
VALUES
(24,1,'NABEEL IMTIAZ'),(25,2,'HAMZA WAHEED'),(26,5,'AKHLAS AHMED'),(27,7,'IKHLAS AHMED'),
(33,3,'MUNEEB UR REHMAN'),(34,4,'HAMZA WAHEED AKHTAR'),(35,5,'IMTIAZ UR REHMAN'),(36,6,'AHSAN ALI'),(37,7,'ABDUL HASEEB'),
(41,2,'ABDUR REHMAN'),(42,3,'HASEEB QAMAR'),(43,6,'ALI HUSSAIN'),
(65,4,'ALI HUSSAIN MUSTAFA'),(66,3,'MUNEEB KHAN'),(67,5,'BAZIL AFTAB'),(68,7,'ZAVIYAR ALI'),
(87,4,'USSAID MUJTABA'),(88,5,'TOOBA AMJAD')

INSERT INTO ACCOUNTS (DEPT,SAL,INCREMENT)
VALUES
(1,500000,10000),(2,250000,7500),(3,75000,5000),(4,350000,8500),(5,300000,7800),(6,50000,1500),(7,100000,2000)


SELECT * FROM DEPT ORDER BY DEPTNO
SELECT * FROM LOC ORDER BY LNO
SELECT * FROM ACCOUNTS ORDER BY DEPT
SELECT * FROM EMPLOY ORDER BY EMPID