Table of Contents
SQL Exercises with Sample Tables and Demo Data – mps
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.
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
- Introduction to C Programming – Setup CodeBlocks
- Learn to Write Your First C Program – Print Hello Computer
- Understand C Variables with Flowcharts and Examples
- C Datatypes Explained with Flowcharts and Examples
- C Operators Learn the Basics of Programming Step by Step
- C Decision Making: If, If-Else, Switch-Case Statements
- C While and Do-While Loops Explained with Examples
- C For Loop – Learn Its Purpose with Flowchart, and Example
- Word Frequency in a Python String | Multiple Techniqes – My programming school
- How C Programming Contributes To Machine Learning and it’s Algorithms – Codeforwin