Question

Q1) Connect to HR database and write the queries to answer the following questions. Provide your queries text and result in a .txt file: a) How many employees you can find in the Employees table b) How many departments are there in this database c) How many employees work for sales department d) What is the average salary of department 50 e) Copy Countries table from HR database into Section 24 using Select Into clause. f) Write a query to select the 5 least paid employees hired before June 1st, 2008 Q2) Submit a script to do the following: Create a student table (Using Create Table) with the following fields. (to name fields in sql scripts replace spaces with underscores). Choose Appropriate data types: student id first name last name email address date of birth program name current semester Add five student records (Using Insert). Choose your examples but have two from Computer Eng program, two of Computer Technician and one in System Analysis Program Delete the System Analysis record (Using Delete statement with Where clause). Update the Computer Technician semester setting it to one (Using Update Statement). Keep the table into your Section 24 database. (In your submission provide your user Id).

Answer 1

1. SELECT COUNT(*) FROM emp_details ;
2. SELECT COUNT(*) FROM department;
3. SELECT COUNT(*) FROM employees WHERE dep_id IN (SELECT dep_id FROM department WHERE dep_name = 'Sales');
4. SELECT avg(salary)FROM employees where department_id= 50;
5. SELECT country FROM employees.
6. CREATE TABLE Student ( StudentID int, first_name char(20) , last_name char(20) , email varchar(15) , DOB DATE, program _name char(20), current_sem int,PRIMARY KEY(StudentID));
7. INSERT INTO Student (StudentID , first_name , last_name , email , DOB , program_name ,current_sem)

VALUES ( (124, john , smith , [email protected] , 1990-3-4, CS , 5),

(125, sam , Curran , [email protected] , 1990-5-3, CS , 6) ,

(126, tom , wiley , [email protected] , 1990-6-4, EC , 5),

(127, john , son , [email protected] , 1990-3-7, EC , 6),

(128 , will , taylor, [email protected], 1990-4-6, EC, 5) );