SQL Exercises with Sample Tables and Demo Data – MPS


SQL Exercises with Sample Tables and Demo Data – mps

sql exercises

Dear mates, we now have once more introduced you a new set of SQL workout routines for follow. This submit first offers SQL instructions to create the required tables and populate demo information.

After operating the beneath instructions, you’re going to get able to execute SQL queries from the beneath workout routines. You could try and run another advanced question as nicely. After that, please do share it with us, and we’ll get it listed in this submit.

SQL Exercises

The part beneath outlines the SQL queries to create check tables and demo information. Execute these instructions to proceed.

Sample Tables and Insert Demo Data

Step-1 (Create Table)

It is higher to create the check information in a devoted and separate database. Hence, let’s first create a database for our testing objective.

CREATE database SQLTest;
USE SQLTest;

Here, you’ll be creating two tables, specifically EMPLOYEE and DEPARTMENT. The workout routines will revolve round them.

CREATE TABLE DEPARTMENT
(
   DEPTCODE   INT(10),
   DeptName   CHAR(30),
   LOCATION   VARCHAR(33)
);

CREATE TABLE EMPLOYEE
(
   EmpCode      INT(4),
   EmpFName     VARCHAR(15),
   EmpLName     VARCHAR(15),
   Job          VARCHAR(45),
   Manager      CHAR(4),
   HireDate     DATE,
   Salary       INT(6),
   Commission   INT(6),
   DEPTCODE     INT(2)
);

Step-2 (Alter Table)

We have now created the specified SQL tables. Next, you need to run the beneath instructions to alter the desk construction. It is typically fairly helpful that you understand how to change present desk properties.

ALTER TABLE DEPARTMENT
ADD PRIMARY KEY (DEPTCODE);

ALTER TABLE DEPARTMENT
CHANGE COLUMN DEPTCODE DEPTCODE INT(10) NOT NULL;

ALTER TABLE DEPARTMENT
CHANGE COLUMN DeptName DeptName CHAR(30) UNIQUE;

ALTER TABLE DEPARTMENT
CHANGE COLUMN LOCATION LOCATION VARCHAR(33) NOT NULL;

ALTER TABLE DEPARTMENT
CHANGE COLUMN DeptName DeptName VARCHAR(15) UNIQUE;

ALTER TABLE EMPLOYEE
ADD PRIMARY KEY (EmpCode);

ALTER TABLE EMPLOYEE
CHANGE COLUMN EmpCode EmpCode INT(4) NOT NULL;

ALTER TABLE EMPLOYEE
ADD FOREIGN KEY (DEPTCODE)
REFERENCES DEPARTMENT(DEPTCODE);

ALTER TABLE EMPLOYEE
CHANGE COLUMN Salary Salary DECIMAL(6,2);

ALTER TABLE EMPLOYEE
ADD COLUMN DOB DATE
AFTER EmpLName;

ALTER TABLE EMPLOYEE
DROP COLUMN DOB;

Step-3 (Populate Table)

Below INSERT assertion will fill the above tables with demo information you should use to run queries.

INSERT INTO DEPARTMENT VALUES (10, 'FINANCE', 'EDINBURGH'),
                              (20,'SOFTWARE','PADDINGTON'),
                              (30, 'SALES', 'MAIDSTONE'),
                              (40,'MARKETING', 'DARLINGTON'),
                              (50,'ADMIN', 'BIRMINGHAM');
                       
INSERT INTO EMPLOYEE  
VALUES (9369, 'TONY', 'STARK', 'SOFTWARE ENGINEER', 7902, '1980-12-17', 2800,0,20),
       (9499, 'TIM', 'ADOLF', 'SALESMAN', 7698, '1981-02-20', 1600, 300,30),    
       (9566, 'KIM', 'JARVIS', 'MANAGER', 7839, '1981-04-02', 3570,0,20),
       (9654, 'SAM', 'MILES', 'SALESMAN', 7698, '1981-09-28', 1250, 1400, 30),
       (9782, 'KEVIN', 'HILL', 'MANAGER', 7839, '1981-06-09', 2940,0,10),
       (9788, 'CONNIE', 'SMITH', 'ANALYST', 7566, '1982-12-09', 3000,0,20),
       (9839, 'ALFRED', 'KINSLEY', 'PRESIDENT', 7566, '1981-11-17', 5000,0, 10),
       (9844, 'PAUL', 'TIMOTHY', 'SALESMAN', 7698, '1981-09-08', 1500,0,30),
       (9876, 'JOHN', 'ASGHAR', 'SOFTWARE ENGINEER', 7788, '1983-01-12',3100,0,20),
       (9900, 'ROSE', 'SUMMERS', 'TECHNICAL LEAD', 7698, '1981-12-03', 2950,0, 20),
       (9902, 'ANDREW', 'FAULKNER', 'ANAYLYST', 7566, '1981-12-03', 3000,0, 10),
       (9934, 'KAREN', 'MATTHEWS', 'SOFTWARE ENGINEER', 7782, '1982-01-23', 3300,0,20),
       (9591, 'WENDY', 'SHAWN', 'SALESMAN', 7698, '1981-02-22', 500,0,30),
       (9698, 'BELLA', 'SWAN', 'MANAGER', 7839, '1981-05-01', 3420, 0,30),
       (9777, 'MADII', 'HIMBURY', 'ANALYST', 7839, '1981-05-01', 2000, 200, NULL),
       (9860, 'ATHENA', 'WILSON', 'ANALYST', 7839, '1992-06-21', 7000, 100, 50),
       (9861, 'JENNIFER', 'HUETTE', 'ANALYST', 7839, '1996-07-01', 5000, 100, 50);

SQL Exercises for Basic to Advanced Queries

#1 Create a question that shows EMPFNAME, EMPLNAME, DEPTCODE, DEPTNAME, LOCATION from EMPLOYEE, and DEPARTMENT tables. Make certain the outcomes are in the ascending order based mostly on the EMPFNAME and LOCATION of the division.

SELECT E.EMPFNAME, E.EMPLNAME, E.DEPTCODE,
       D.DEPTNAME, D.LOCATION
       FROM EMPLOYEE E, DEPARTMENT D
       WHERE E.DEPTCODE = D.DEPTCODE
       ORDER BY E.EMPFNAME, D.LOCATION;

#2 Display EMPFNAME and “TOTAL SALARY” for every worker

SELECT EMPFNAME, SUM(COMMISSION+SALARY) AS "TOTAL SALARY" FROM EMPLOYEE GROUP BY EMPCODE;

#3 Display MAX and 2nd MAX SALARY from the EMPLOYEE desk.

SELECT
(SELECT MAX(SALARY) FROM EMPLOYEE) MAXSALARY,
(SELECT MAX(SALARY) FROM EMPLOYEE
WHERE SALARY NOT IN (SELECT MAX(SALARY) FROM EMPLOYEE )) as 2ND_MAX_SALARY;

#4 Display the TOTAL SALARY drawn by an analyst working in dept no 20

SELECT SUM(SALARY+COMMISSION) AS TOTALSALARY FROM EMPLOYEE
WHERE JOB = 'ANALYST' AND DEPTCODE = 20;

#5 Compute common, minimal and most salaries of the group of workers having the job of ANALYST.

SELECT AVG(Salary) AS AVG_SALARY, MIN(Salary) AS MINSALARY, MAX(Salary) AS MAXSALARY
FROM EMPLOYEE WHERE Job = 'ANALYST';

Please word that we’ll be including more and more SQL queries to this submit based mostly in your suggestions. So, please do share your questions with us.

SQL Exercises with Sample Tables and Demo Data – mps | SQL Exercises with Sample Tables and Demo Data – mps

C Programming Recommended Post

Source link

Have any Question or Comment?

Leave a Reply

Your email address will not be published. Required fields are marked *

Categories

You have successfully subscribed to myprogrammingschool

There was an error while trying to send your request. Please try again.

My Programming School will use the information you provide on this form to be in touch with you and to provide updates and marketing.