SQL Practice Set for assignment of UG PG

SQL Practice Set for assignment of UG PG

Consider two table given below.
Practice 1:
Write down the SQL code for above EMPLOYEE and DEPARTMENT Schema, and insert Some data also.
Solution:
Creating employee table-> 
-------------------------------------------
CREATE TABLE employee
 (
  Eno char(3) NOT NULL,
  Ename varchar(50) NOT NULL,
  Job_type varchar(50) NOT NULL,
  Manager char(3) DEFAULT NULL,
  Hire_date date NOT NULL,
  Dno int(11) DEFAULT NULL,
  Commission decimal(10,2) DEFAULT NULL,
  Salary decimal(7,2) NOT NULL,
  PRIMARY KEY (Eno),
  CONSTRAINT Dno FOREIGN KEY (Dno) REFERENCES department (Dno),
  CONSTRAINT Manager FOREIGN KEY (Manager) REFERENCES employee (Eno)
)


Inserting data into employee table-> 
-------------------------------------------

INSERT INTO employee VALUES ('736','Smith','Clerk','790','1981-12-17',20,0.00,1000.00),
('749','Allan','Sales_man','769','1981-02-20',30,300.00,2000.00),
('752','Ward','Sales_man','769','1981-02-22',30,500.00,1300.00),
('756','Jones','Manager','783','1981-04-02',20,0.00,2300.00),
('765','Martin','Sales_man','784','1981-04-22',30,1400.00,1250.00),
('769','Blake','Manager','783','1981-05-01',30,0.00,2870.00),
('778','Clark','Manager','783','1981-06-09',10,0.00,2900.00),
('783','King','President',NULL,'1981-11-17',10,0.00,2950.00),
('784','Turner','Sales_man','769','1981-09-08',30,0.00,1450.00),
('787','Adams','Clerk','778','1983-01-12',20,0.00,1150.00),
('788','Scott','Analyst','756','1982-12-09',20,0.00,2850.00),
('790','James','Clerk','769','1981-12-03',30,0.00,950.00),
('792','Ford','Analyst','756','1981-12-03',20,0.00,2600.00),
('793','Miller','Clerk','788','1982-01-23',40,0.00,1300.00);


Creating department table-> 
-------------------------------------------
    CREATE TABLE department
     (
      Dno int(11) NOT NULL,
      Dname varchar(50) DEFAULT NULL,
      Location varchar(50) DEFAULT NULL,
      PRIMARY KEY (Dno)
     )
 

Inserting data into department table-> 
-------------------------------------------

   INSERT INTO department VALUES
    (10,'Accounting','New York'),
    (20,'Research','Dallas'),
    (30,'Sales','Chicago'),
    (40,'Operation','Boston'),
    (50,'Marketing','New Delhi');
Practice 2:
Write SQL query to display the Employee Name and Salary of all the employees earning more than $2850.
Answer:
SQL code: 
SELECT Ename, Salary FROM employee WHERE (Commission + Salary) > 2850;
Practice 3:
Write SQL query to display all the data from the Employee Table. Separate each Column by a comma and name the said column as THE_OUTPUT.
Answer:
SQL code: 
SELECT CONCAT(Eno , ', ', Ename, ',', Job_type, ', ',Manager, ',' ,Hire_date, ',' ,Dno, ',' ,Commission, ',' ,Salary) AS THE_OUTPUT FROM employee;
Practice 4:
Write SQL query to display the Employee Name concatenated by a Job separated by a comma.
Answer:
SQL code: 
SELECT CONCAT(Ename, ',', Job_type) AS Name_Job FROM employee;
Practice 5:
Write SQL query to display unique Jobs from the Employee Table.
Answer:
SQL code: 
SELECT DISTINCT Job_type FROM employee;
Practice 6:
Write SQL query to display Employee Name, Job, Hire Date, Employee Number; for each employee with the Employee Number appearing first.
Answer:
SQL code:
SELECT Eno, Ename, Job_type, Hire_date FROM employee;
Practice 7:
Write SQL query to display Employee Name and Department Number for the Employee No= 7900.
Answer:
SQL code:
SELECT Ename,Dno FROM employee WHERE Eno='7900';
Practice 8:
Write SQL query to display Employee Name and Salary for all employees whose salary is not in the range of $1500 and $2850.
Answer:
SQL code:
SELECT Ename,Salary FROM employee WHERE Salary NOT BETWEEN 1500 AND 2850;
Practice 9:
Write SQL query to display Employee Name and Department No. Of all the employees in Dept 10 and Dept 30 in the alphabetical order by name.
Answer:
SQL code:
SELECT Ename,Dno FROM employee WHERE Dno=10 OR DNO=30 ORDER BY Ename;
Practice 10:
Write SQL query to display Name and Hire Date of every Employee who was hired in 1981.
Answer:
SQL code:
SELECT Ename,Hire_date FROM EMPLOYEE WHERE Hire_date LIKE '1981%';
Practice 11:
Write SQL query to display Name and Job of all employees who don’t have a current Manager.
Answer:
SQL code:
SELECT Ename,Job_type FROM employee WHERE Manager IS NULL;
Practice 12:
Write SQL query to display the Current Date.
Answer:
SQL code:
SELECT CURDATE();
Practice 13:
Write SQL query to display Name, Salary and Commission for all employees whose Commission Amount is greater than their Salary increased by 5%.
Answer:
SQL code:
SELECT Ename, Salary, Commission FROM employee WHERE Commission > (Salary+Salary*0.05);
Practice 14:
Write SQL query to display Name of all employees either have two ‘R’s or have two ‘A’s in their name and are either in Dept No = 30 or their Manger’s Employee No = 778.
Answer:
SQL code:
SELECT Ename, Dno, Manager FROM employee WHERE Ename LIKE '%A%A%' OR Ename LIKE '%R%R%' AND Dno=30 OR Manager='778';
Practice 15:
Write SQL query to display Name of all the employees where the third letter of their name is ‘A’.
Answer:
SQL code:
SELECT Ename FROM employee WHERE Ename LIKE '_ _ A%';
Practice 16:
Write SQL query to Sort the data in descending order of Salary and Commission.
Answer:
SQL code:
SELECT Eno, Ename, Job_type, Manager, Hire_date, Dno, Commission, Salary FROM employee ORDER BY Salary DESC,Commission DESC;
Practice 17:
Write SQL query to display the Name, Salary and Commission for all the employees who earn commission.
Answer:
SQL code:
SELECT Ename,Salary,Commission FROM employee WHERE Commission > 0.00;

SQL Practice Set for assignment of UG PG

Practice 18:
Write SQL query to display Name, Hire Date and Salary Review Date which is the 1st Monday after six months of employment.
Answer:
SQL code:
SELECT Ename, Hire_date, date_add(date_add(Hire_date,INTERVAL 6 MONTH),INTERVAL (7-WEEKDAY(date_add(Hire_date,INTERVAL 6 MONTH))) DAY) AS REVIEW_DATE FROM employee;
Practice 19:
Write SQL query to display Name and calculate the number of months between today and the date each employee was hired.
Answer:
SQL code:
SELECT Ename,12 *  (YEAR(curdate())-YEAR(Hire_date)) + (MONTH(CURDATE())-MONTH(Hire_date)) AS MONTHS FROM employee;
Practice 20:
Write SQL query to display the following for each employee:- earns < Salary> monthly but wants < 3 * Current Salary >. Label the Column as Dream Salary.
Answer:
SQL code:
SELECT CONCAT(Ename,' earns ',Salary,' monthly but wants ',3*Salary) AS DREAM_SALARY FROM employee;
Practice 21:
Write SQL query to display Name with the 1st letter capitalized and all other letter lower case and length of their name of all the employees whose name starts with ‘J’, ’A’ and ‘M’.
Answer:
SQL code:
SELECT CONCAT( UPPER(SUBSTRING(Ename,1,1)) , LOWER(SUBSTRING(Ename,2,50))) AS NAME,LENGTH(Ename) AS LENGTH  FROM employee WHERE Ename LIKE 'J%' OR Ename LIKE 'A%' OR Ename LIKE 'M%';
Practice 22:
Write SQL query to display Name, Hire Date and Day of the week on which the employee started.
Answer:
SQL code:
SELECT Ename, Hire_date, DAYNAME(Hire_date) AS WEEK_DAY FROM employee;
Practice 23:
Write SQL query to display the no. Of managers without listing their names.
Answer:
SQL code:
SELECT COUNT(DISTINCT Manager) FROM employee;
Practice 24:
Write SQL query to display the number of employees performing the same Job type functions.
Answer:
SQL code:
SELECT job_type,COUNT(*) FROM employee GROUP BY Job_type;
Practice 25:
Write SQL query to display the Highest, Lowest, Sum and Average Salaries of all the employees
Answer:
SQL code:
SELECT MAX(Salary),MIN(Salary),SUM(Salary),AVG(Salary) FROM employee;
Practice 26:
Write SQL query to display Name and Salaries represented by asterisks, where each asterisk (*) signifies $100.
Answer:
SQL code:
SELECT Ename, REPEAT ('*',(Salary/100)) AS SALARY_IN_STAR FROM employee;
Practice 27:
Write SQL query to display Name, Dept No. And Salary of any employee whose department No. And salary matches both the department no. And the salary of any employee who earns a commission.
Answer:
SQL code:
SELECT Ename,Dno,Salary FROM employee WHERE (Dno,Salary) IN (SELECT Dno,Salary FROM employee WHERE Commission>0);
Practice 28:
Write SQL query to display Name and Employee no. Along with their Manger’s Name and the Manager’s employee no; along with the Employees’ Name who do not have a Manager.
Answer:
SQL code:
SELECT e.Ename, e.Eno, d.Ename, d.Eno FROM employee AS e LEFT OUTER JOIN employee as d ON e.Eno=d.Manager;
Practice 29:
Write SQL query to display Name, Job, Department No. And Department Name for all the employees working at the Dallas location.
Answer:
SQL code:
SELECT e.Ename, e.Job_type, e.Dno, d.Dname FROM employee AS e,department as d WHERE e.Dno=d.Dno AND d.Location='Dallas';
Practice 30:
Write SQL query to display Name, Dept Name of all employees who have an ‘A’ in their name.
Answer:
SQL code:
SELECT e.Ename, d.Dname FROM employee AS e,department as d WHERE e.Ename LIKE '%A%' AND e.Dno=d.Dno;
Practice 31:
Write SQL query to display Unique Listing of all Jobs that are in Department # 30.
Answer:
SQL code:
SELECT DISTINCT Job_type FROM employee WHERE Dno=30;
Practice 32:
Write SQL query to display Name, Department Name and Department No for all the employees.
Answer:
SQL code:
SELECT e.Ename,d.Dname,e.Dno FROM employee AS e,department AS d WHERE e.Dno=d.Dno;
Practice 33:
Write SQL query to display the department no, name and job for all employees in the Sales department.
Answer:
SQL code:
SELECT e.Dno,e.Ename,e.Job_type FROM employee AS e,department as d WHERE d.Dno=e.Dno AND d.Dname='Sales';

SQL Practice Set for assignment of UG PG

Practice 34:
Write SQL query to display the names and salaries of all employees who report to King.
Answer:
SQL code:
SELECT Ename,Salary FROM employee WHERE Manager=(SELECT Eno FROM employee WHERE Ename='King');
Practice 35:
Write SQL query to display Employee Number and Name for all employees who work in a department with any employee whose name contains a ‘T’.
Answer:
SQL code:
SELECT e.Eno,e.Ename FROM employee AS e ,employee as d WHERE e.Manager=d.Eno AND d.Ename LIKE '%T%';
Practice 36:
Write SQL query to display the Employee No. And Name for all employees who earn more than the average salary.
Answer:
SQL code:
SELECT Eno,Ename FROM employee WHERE Salary > (Select AVG(Salary) FROM employee);
Practice 37:
Write SQL query to display Name and Hire Date for all employees in the same dept. As Blake.
Answer:
SQL code:
SELECT Ename,Hire_date FROM employee WHERE Dno=(SELECT Dno FROM employee WHERE Ename='Blake');
Practice 38:
Write SQL query to display the Department Name, Location Name, No. Of Employees and the average salary for all employees in that department.
Answer:
SQL code:
SELECT d.Dname,d.Location,AVG(e.Salary),COUNT(*) FROM employee AS e,department AS d WHERE d.Dno=e.Dno GROUP BY d.Dname;
Do you want to crack NTA NET JRF/GATE CSE

NTA UGC NET Computer Science

Leave a Reply

Your email address will not be published.

error: Content is protected !!
Open chat
1
Hi,how Can We Help You ?