Answer Happy

Need queries and screenshots asap on my SQL
Use the tables created in Lab 04 to complete this
assignment.
1. Count the number of employees in each department. Also,
display the maximum salary of each department.
2. Display the id, full name, designation, and salary of the
lowest paid employee.
3. Display the names of all the designations along with the
employees assigned to that designation (irrespective of whether the
designation has been assigned to someone or not).
4. List all the countries with more than two departments. Also,
give the total number of departments in that country.
5. List all the cities to which the employees belong to or where
the departments exist.
6. List all the departments along with their employees’ names
(irrespective of whether any of the values is NULL).
Tables of lab4:
1.
Create table Location(locationID varchar(20) primary key, city
varchar(20), country varchar(20));
Insert into Location Values( '1000' , 'Roma', 'USA');
Insert into Location Values( '1001' , 'Venice', 'France');
Insert into Location Values( '1002' , 'Tokyo', 'Japan');
Insert into Location Values( '1003' , 'Hiroshima', 'Japan');
Select * from Location;
2.
Create table Job(jobID varchar(20) primary key, jobTitle
varchar(20), salary int);
Insert into Job Values( 'ENG123' , 'engineer', 20000);
Insert into Job Values( 'MAN456' , 'Manager', 65000);
Insert into Job Values( 'IT0879' , 'Software Eng', 90000);
Insert into Job Values( 'ANA789' , 'Analyst', 78000);
Select * from Job;
3.
Create table Department(deptID varchar(20) primary key, deptName
varchar(20), locationID varchar(20) references Location(locationID)
ON DELETE CASCADE);
Insert into Department values('D001','HR','1000');
Insert into Department values('D002','IT','1001');
Insert into Department values('D003','Finance','1001');
Insert into Department values('D004','Sales','1003');
4.
Create table Employee(employeeID varchar(20) primary key,
firstName varchar(20) NOT NULL, lastName varchar(20) NOT NULL,
hiringDate varchar(20), deptID varchar(20) references
Department(deptID), jobID varchar(20) references Job(jobID));
Insert into Employee
values('E001','John','King','12-2-2000','D001','ENG123' );
Insert into Employee
values('E002','Smith','Parul','1-2-2005','D002','MAN456' );
Insert into Employee
values('E003','Blake','King','5-12-2006','D002','IT0879' );
Insert into Employee
values('E004','Rick','John','12-2-2000','D003', 'ANA789' );
Select * from Employee ;
2. Ensure that all numerical IDs increment automatically. Show
the relevant table structures.
CREATE SEQUENCE seq
START WITH 1000
INCREMENT BY 1
MAXVALUE 1000000
3. Set the location field to hold the value “Karachi, Pakistan”
unless otherwise specified. Show the relevant table structures.
Insert into Location Values( '1004' , 'Karachi',
'Pakistan');
4. Insert at least 4 relevant records in each table. Display all
the data.
Already Insert
5. Ensure that employee names, department names and job titles
are never empty. Show the relevant table structures.
Create table Employee(employeeID varchar(20) primary
key, firstName varchar(20) NOT NULL, lastName
varchar(20) NOT NULL, hiringDate varchar(20),
deptID varchar(20) references Department(deptID), jobID varchar(20)
references Job(jobID));
6.
Delete any location and ensure that all departments at that
location have their locations set to null. Show all relevant
queries, table structures, and data.
delete location where locationID ='1000';
LOCATIONID 1000 1001 1002 1003 Download SY COUNTRY Roma USA Venice France Tokyo Japan Hiroshima Japan CITY
JOBID ENG123 engineer MAN456 Manager IT0879 Software Eng ANA789 Analyst JOBTITLE Download CSV SALARY 20000 65000 90000 78000
DEPTID DEPTNAME D001 HR D802 D003 D884 Download CSV IT Finance Sales LOCATIONID 1000 1001 1001 1003
EMPLOYEEID FIRSTNAME LASTNAME HIRINGDATE DEPTID JOBID E001 John King 12-2-2000 D001 ENG123 E002 Smith Parul 1-2-2005 D802 MAN456 E003 Blake King 5-12-2006 D802 IT0879 E004 Rick John 12-2-2000 D803 ANA789 Download CSV 4 rows selected.
DEPTID DEPTNAME D801 HR D882 IT D003 Finance D884 Sales Download CSV LOCATIONID 1001 1001 1003