Question

Q.5] Answer the following questions based on the company database (based on the homework assignment 2) y database (based on the homework assignment 2) 3 IPage 1. For each department whose average employee salary is more than $30,000, write a SQL statement to retrieve the department name and the number of employees working for that department 2. Write a SQL statement to retrieve first name, last name, and address of each employee who lives in Houston, TX. 3. Write a SQL statement to retrieve the names of employees who have no dependents (use EXISTS clause) 4. Write a SQL statement to create a view Deptinfo which includes DeptName, NoOfEmployees, TotalSalary. Note: use aggregate functions, group by Homework Assignment 2 In this homework, you will practice how to create company database schema (DBMS specific database or physical database) and its table schemas. You also practice how to populate database tables. You then study how to use ER diagramming tool and forward engineer tool available in the MySQL Workbench. Study SQL scripts explained in chapters 3,4, and 5. Step 1: create database named company by using MySQLWorkbench SQL editor or MySQL command shell. Step 2: download SQL scripts and data to be populated (archived in companyDB.zip) from the course home page and save them in a directory (e.g.. /usr/home/gjung/MyDBs /companyTest or C:MyDBslcompanyTest). Change directory to MyDBs/company, and type source source.sq: Then database company tables are created in MySQL. The company database tables are populated by loading data from CSV (comma separated value) files by the SQL scripts. See the content of source.sql to understand physical database generation (including data population). To enter mysql CLI (we assume your PATH environment variable is properly set to the folder where mysql command is stored): In Windows: Smysql-u username p //then enter password In UNIX: Smysql -local-infile -u username p//then enter password Step 3: Open companyTest.mwb file with MySQL Workbench, and see the tables and their relationships in the MySQL Workbench. Study how to generate SOL script by forward engineer tool available in the MySQL Workbench. Step 4: Preview and practice SQL query examples which will be studied in chapter 4 and chapter 5

Answer 1

--1

Select d.dname,count(e.ssn) From department d inner join employee e on d.dnumber=e.dno group by d.dname having Avg(e.salary)>30000;

We have joined department and emploee table to get dname and used group by department to get count and avg of salary. Having clause helps to filter data.

--2

select fname,lname , Address from employee where address like '%Houston, Tx%';

with help of wild character % and like condition we are checking for string in address column data.

--3

select fname from employee where exist (select dno from employee e1 on employee.dno=e.dno and e1.dno is null);

Above query we have used exist operations and null condition to get employees names whose dno is null;

--4

Select d.dname,count(e.ssn) As NumOfEmployee,Sum(e.salary) As TotalSalary From department d left join employee e on d.dnumber=e.dno group by d.dname;