Scribd
Upload
5 views3 pages

SQL Complete Master

The document provides a comprehensive guide on SQL, covering the structure of SQL queries, the creation of a database with four interconnected tables (Departments, Employees, Projects, Assignments), and sample data for each table. It includes complex query examples with explanations, demonstrating various SQL operations such as joins, aggregations, and filtering. The final concept emphasizes a systematic approach to constructing SQL queries by identifying tables, relationships, and desired outputs.

Uploaded by

gamedb78
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
5 views3 pages

SQL Complete Master

The document provides a comprehensive guide on SQL, covering the structure of SQL queries, the creation of a database with four interconnected tables (Departments, Employees, Projects, Assignments), and sample data for each table. It includes complex query examples with explanations, demonstrating various SQL operations such as joins, aggregations, and filtering. The final concept emphasizes a systematic approach to constructing SQL queries by identifying tables, relationships, and desired outputs.

Uploaded by

gamedb78
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

SQL COMPLETE MASTER GUIDE (MULTI-TABLE +

COMPLEX QUERIES)

1. SQL STRUCTURE
SELECT columns
FROM table1
JOIN table2 ON condition
JOIN table3 ON condition
JOIN table4 ON condition
WHERE condition
GROUP BY columns
HAVING condition
ORDER BY columns;

2. DATABASE (4 CONNECTED TABLES)

CREATE TABLE Departments (


dept_id INT PRIMARY KEY,
dept_name VARCHAR(50)
);

CREATE TABLE Employees (


emp_id INT PRIMARY KEY,
name VARCHAR(50),
salary INT,
dept_id INT,
FOREIGN KEY (dept_id) REFERENCES Departments(dept_id)
);

CREATE TABLE Projects (


project_id INT PRIMARY KEY,
project_name VARCHAR(50),
dept_id INT,
FOREIGN KEY (dept_id) REFERENCES Departments(dept_id)
);

CREATE TABLE Assignments (


emp_id INT,
project_id INT,
hours INT,
PRIMARY KEY (emp_id, project_id),
FOREIGN KEY (emp_id) REFERENCES Employees(emp_id),
FOREIGN KEY (project_id) REFERENCES Projects(project_id)
);

3. SAMPLE DATA (MIN 10 ROWS)

INSERT INTO Departments VALUES


(1,'HR'),(2,'IT'),(3,'Sales'),(4,'Finance');

INSERT INTO Employees VALUES


(1,'A',50000,1),(2,'B',60000,2),(3,'C',55000,3),(4,'D',70000,2),
(5,'E',45000,1),(6,'F',80000,4),(7,'G',30000,3),(8,'H',90000,2),
(9,'I',65000,4),(10,'J',72000,1);

INSERT INTO Projects VALUES


(1,'P1',1),(2,'P2',2),(3,'P3',3),(4,'P4',4),
(5,'P5',2),(6,'P6',1),(7,'P7',3),(8,'P8',4),
(9,'P9',2),(10,'P10',1);

INSERT INTO Assignments VALUES


(1,1,5),(2,2,10),(3,3,15),(4,2,20),(5,1,8),
(6,4,12),(7,3,7),(8,5,25),(9,4,18),(10,6,9);
4. COMPLEX QUERIES WITH EXPLANATION

Q1: Employee + Department + Project

SELECT [Link], d.dept_name, p.project_name


FROM Employees e
JOIN Departments d ON e.dept_id = d.dept_id
JOIN Assignments a ON e.emp_id = a.emp_id
JOIN Projects p ON a.project_id = p.project_id;

Explanation: Chain join across 4 tables using foreign keys.

Q2: Highest salary employee


SELECT * FROM Employees
WHERE salary = (SELECT MAX(salary) FROM Employees);

Q3: Department with more than 2 employees

SELECT d.dept_name, COUNT(e.emp_id)


FROM Departments d
JOIN Employees e ON d.dept_id = e.dept_id
GROUP BY d.dept_name
HAVING COUNT(e.emp_id) > 2;

Q4: Employees working more than 10 hours

SELECT [Link], [Link]


FROM Employees e
JOIN Assignments a ON e.emp_id = a.emp_id
WHERE [Link] > 10;

Q5: Average salary per department

SELECT d.dept_name, AVG([Link])


FROM Departments d
JOIN Employees e ON d.dept_id = e.dept_id
GROUP BY d.dept_name;

Q6: Employees not assigned to any project (LEFT JOIN)

SELECT [Link]
FROM Employees e
LEFT JOIN Assignments a ON e.emp_id = a.emp_id
WHERE a.emp_id IS NULL;

Q7: Order by salary

SELECT name, salary FROM Employees ORDER BY salary DESC;

Q8: Employees in IT department

SELECT name FROM Employees


WHERE dept_id = (SELECT dept_id FROM Departments WHERE dept_name='IT');

Q9: Count projects per department

SELECT d.dept_name, COUNT(p.project_id)


FROM Departments d
JOIN Projects p ON d.dept_id = p.dept_id
GROUP BY d.dept_name;

Q10: Multi-condition query

SELECT [Link], p.project_name


FROM Employees e
JOIN Assignments a ON e.emp_id = a.emp_id
JOIN Projects p ON a.project_id = p.project_id
WHERE [Link] > 50000 AND [Link] > 10;

5. FINAL CONCEPT

Follow: Identify → Tables → Relationships → JOIN → Filter → Group → Output

You might also like