SQL (Structured Query Language) is a standard language for managing and manipulating relational databases. It allows users to create, read, update, and delete database records. SQL is widely used for querying data, defining schemas, and ensuring data integrity in systems like MySQL, Oracle, and PostgreSQL. While using Microsoft SQL use “ ` “ symbol when using the name of the column.
• To Create a Table
To define a table structure, use the following SQL query:
create database first;
show databases;
use first;
Create table employee(ssn varchar(10) primary key, Name char(30) Not Null, phone int, doj Date Not Null);
(system,usual small05)
• Printing All Records in a Table
To display the table content, use:
SELECT * FROM first;
• Inserting Data into the Table
To insert new records into the table:
insert into employee
2 values(2,'tommy shelby',91,'12-may-24'),
(3,'john',91,'12-may-2024');
op>1 row created.
• Updating a Column with New Data
To modify existing data in a table:
update employee
2 set age='21'
3 where name='john doe';
• Deleting a Specific Row
To remove a row from the table:
SQL> delete
2 from employee
3 where ssn='3';
• Dropping the Entire Table
To delete the table completely:
drop table employee;
• Updating All Values in a Row
To update all values for a specific row:
SQL> update employee
2 set phone='30';
General Syntax for SELECT Statement
select attribute
from table_name
where condition;
• Adding a New Column to an Existing Table
To add and set values in a new column:
update employee
2 set age=30
3 where ssn=1;
4 ;
• Selecting Data Using Alias for Tables
To refer to tables with aliases:
SQL> select e.name
2 from employee e,emp em
3 where e.ssn=em.ssn;
• Union Operator
To combine results of two queries:
SQL> select * from a
2 union
3 select * from b;
• Intersection Operator
To return common records between two queries:
SQL> select * from a
2 intersect
3 select * from b;
Example with conditions:
SQL> select a.x
2 from a a,b b
3 where a.x=b.x and b.y>2;
• Nested Queries (Subqueries)
To use queries inside another query:
SQL> select x
2 from a
3 where y in (select y from b where y<=10);
Here the function checks if y is equal to b.y.
• Installing MySQL on WSL
To set up MySQL on WSL (Windows Subsystem for Linux):
sudo apt update
sudo apt install mysql-server
• Running MySQL
To start and check MySQL status:
sudo systemctl status MySQL # Check if active and running
sudo MySQL
create database first;
show databases;
use first;
describe first;
• Adding a New Column
To insert a new column into a table:
Alter table table_name add column_name datatype;
• Deleting Specific Rows
To delete data from a table:
delete from first where id==1;
• Bulk Update in a Table
To update multiple rows conditionally:
update first
-> set id=case
-> when `index`=1 then 101
-> when `index`=2 then 102
-> when `index`=3 then 103
-> when `index`=4 then 104
-> else id
-> end;
• Union Query
To combine index and name from two tables:
select `index`,`name` from first
-> union
-> select `index`,`name` from second;
• Intersection Query
To find common records:
select `index` from first intersect select `index` from second;
• Minus Operation
To find records present in one table but not in another:
select * from first where `index` not in(select `index` from second);
• Between Operator
To fetch rows in a specified range:
select `name` from first where `index` between 1 and 3;
• Calculated Value in SELECT Query
To calculate and display new values based on columns:
SELECT `name`, (`id` + `id` + 3) AS calculated_value
-> FROM first
-> WHERE `index` > 1;
Here, calculated_value applies a new condition.
• Nested Queries (Example)
To use subqueries for advanced queries:
select `name`
from emp
where fix=(select `fid`
from std
where `sal`>(select `sal`
from std
where `dept` = 'AIML'));
• Aggregate Functions
These functions help summarize query results:
Sum (int) -select sum(salary) from emp;
Avg (int) -select avg(salary) from emp;
MAX (int,char) -WHERE Salary = (SELECT MAX(Salary) FROM EMPLOYEE);
MIN (int,char) -WHERE Salary = (SELECT MIN(Salary) FROM EMPLOYEE);
Count (int,float,char) -count(*) as total_sales from sales;Example:
select `name`
from first
when 2<=count(select `D ssn`
from dep D emp E
when D `essn`=E `Essn`);
• Grouping Query Results
To group data and apply conditions:
selectfromwheregroup by(e.g.,group by dno)having(e.g.,having count(*)>=1)order by(e.g., ascending order)
Example:
select `dno`,count(*),avg(`salary`)
from emp
grouping by `dno`
having count(*)>=1
order by asc;
Here are some example questions with answers to solve.
Q1.Consider the Sailor database given below. The primary keys are underlined. Assume relevant data types for attributes. SAILORS(Sid, Sname, Rating, Age) BOATS(Bid, Bname, Colour) RESERVES(Sid, Bid, day) Create the above tables in SQL. Specify primary and foreign keys properly. Enter at least 5 tuples in each table with relevant data. Solve the following queries. i. Find the names of sailors who have reserved at least one boat. ii. Find the Sid’s of sailors who have reserved a red or a green boat. iii. Find the Sid’s of sailors who have not reserved a boat.
create table sailors(sid int primary key, sname varchar(20),rating int,age int);
create table boats(bid int primary key,bname varchar(20), color varchar(20));
create table reserves(sid int,bid int, day primary key, foreign key(sid) references sailors(sid), foreign key(bid) references sailors(sid));
insert into sailors(sid,sname,rating,age) values(1,'a',1,23),(2,'b',2,23),(3,'c',3,23);
insert into boats(bid,bname,color)values (1,'a','red'),(2,'b','green'),(3,'c','purple');
insert into reserves(sid,bid,day) values (1,1,1),(2,2,2),(3,NULL,3);
//Names of sailors who have reserved a boat
select s.sname from sailors s, boats b, reserves r where s.sid = r.sid and b.bid = r.bid;
//Sid of sailors who have reserved a green or red boats
select r.sid from reserves r where r.bid in (select b.bid from boats b where b.color in ('red') or b.color in('green'));
//Where no sailor has reserved a boat
select sid from reserves where bid is NULL;
Boats
bid bname color
1 a red
2 b green
3 c purple
Reserves
sid bid day
1 1 1
2 2 2
3 3
Sailors
sid sname rating age
1 a 1 23
2 b 2 23
3 c 3 23
Here is the result for the queries.
Q2.Consider the Employee database given below. The primary keys are underlined. Assume relevant data types for attributes. EMPLOYEE (Fname, Lname, SSN, Addrs, Sex, Salary, SuperSSN, Dno) DEPARTMENT (Dname, Dnumber, MgrSSN, MgrStartDate) PROJECT(Pno, Pname, Dnum) WORKS_ON (ESSN, Pno, Hours) Create the above tables in SQL. Specify primary and foreign keys properly. Enter at least 5 tuples in each table with relevant data. Solve the following queries.i. Retrieve the name of all employees whose salary is greater than the salary of all employees in dept 5. ii. Retrieve the ssn of all employees who work on project numbers 1,2 or 3 iii. Display the total Number of hours put in by all employees on every project.
Employee DB
-- Create EMPLOYEE table
CREATE TABLE EMPLOYEE (
Fname VARCHAR(50),
Lname VARCHAR(50),
SSN CHAR(9) PRIMARY KEY,
Addrs VARCHAR(100),
Sex CHAR(1),
Salary DECIMAL(10, 2),
SuperSSN CHAR(9),
Dno INT
);
-- Create DEPARTMENT table
CREATE TABLE DEPARTMENT (
Dname VARCHAR(50),
Dnumber INT PRIMARY KEY,
MgrSSN CHAR(9),
MgrStartDate DATE,
FOREIGN KEY (MgrSSN) REFERENCES EMPLOYEE(SSN)
);
-- Create PROJECT table
CREATE TABLE PROJECT (
Pno INT PRIMARY KEY,
Pname VARCHAR(50),
Dnum INT,
FOREIGN KEY (Dnum) REFERENCES DEPARTMENT(Dnumber)
);
-- Create WORKS_ON table
CREATE TABLE WORKS_ON (
ESSN CHAR(9),
Pno INT,
Hours DECIMAL(4, 1),
PRIMARY KEY (ESSN, Pno),
FOREIGN KEY (ESSN) REFERENCES EMPLOYEE(SSN),
FOREIGN KEY (Pno) REFERENCES PROJECT(Pno)
);
-- Insert data into EMPLOYEE
INSERT INTO EMPLOYEE VALUES
('John', 'Doe', '111223333', '123 Elm St', 'M', 60000.00, '123456789', 1),
('Jane', 'Smith', '222334444', '456 Oak St', 'F', 75000.00, '123456789', 2),
('Alice', 'Johnson', '333445555', '789 Pine St', 'F', 80000.00, '222334444', 1),
('Bob', 'Brown', '444556666', '101 Maple St', 'M', 55000.00, '111223333', 3),
('Eve', 'Davis', '555667777', '202 Birch St', 'F', 70000.00, '111223333', 2);
-- Insert data into DEPARTMENT
INSERT INTO DEPARTMENT VALUES
('HR', 1, '111223333', '2020-01-15'),
('IT', 2, '222334444', '2019-05-01'),
('Finance', 3, '333445555', '2021-07-20'),
('Marketing', 4, '444556666', '2022-03-10'),
('Operations', 5, '555667777', '2021-11-25');
-- Insert data into PROJECT
INSERT INTO PROJECT VALUES
(101, 'Project Alpha', 1),
(102, 'Project Beta', 2),
(103, 'Project Gamma', 3),
(104, 'Project Delta', 4),
(105, 'Project Epsilon', 5);
-- Insert data into WORKS_ON
INSERT INTO WORKS_ON VALUES
('111223333', 101, 20.5),
('222334444', 102, 35.0),
('333445555', 103, 15.0),
('444556666', 104, 40.0),
('555667777', 105, 25.0);
select fname from employee where salary>(select max(salary) from employee where dno=3);
select e.ssn from employee e, works_on w where e.ssn=w.essn and w.pno in (101,102,103);
select sum(hours) from works_on;
Q3.Consider the Aircraft database given below. The primary keys are underlined. Assume relevant data types for attributes. AIRCRAFT (Aircraft ID, Aircraft_name, Cruising_range) CERTIFIED (Emp ID, Aircraft ID) EMPLOYEE (Emp ID, Ename, Salary) Create the above tables in SQL. Specify primary and foreign keys properly. Enter at least 5 tuples in each table with relevant data. Solve the following queries. i. Find the employee ID’s of employee who make the highest salary. ii. Find the name of aircrafts such that all pilots certified to operate them earn more than 50000 iii. Find the employees who are not certified for operating any aircraft.
Aircraft
-- Create AIRCRAFT table
CREATE TABLE AIRCRAFT (
Aircraft_ID INT PRIMARY KEY,
Aircraft_name VARCHAR(50),
Cruising_range INT
);
-- Create EMPLOYEE table
CREATE TABLE EMPLOYEE (
Emp_ID INT PRIMARY KEY,
Ename VARCHAR(50),
Salary DECIMAL(10, 2)
);
-- Create CERTIFIED table
CREATE TABLE CERTIFIED (
Emp_ID INT,
Aircraft_ID INT,
PRIMARY KEY (Emp_ID, Aircraft_ID),
FOREIGN KEY (Emp_ID) REFERENCES EMPLOYEE(Emp_ID),
FOREIGN KEY (Aircraft_ID) REFERENCES AIRCRAFT(Aircraft_ID)
);
-- Insert data into AIRCRAFT
INSERT INTO AIRCRAFT VALUES
(1, 'Boeing 747', 8000),
(2, 'Airbus A320', 6100),
(3, 'Cessna 172', 1300),
(4, 'Concorde', 4500),
(5, 'Gulfstream G550', 6750);
-- Insert data into EMPLOYEE
INSERT INTO EMPLOYEE VALUES
(101, 'John Smith', 75000.00),
(102, 'Alice Johnson', 82000.00),
(103, 'Bob Brown', 60000.00),
(104, 'Eve Davis', 95000.00),
(105, 'Charlie White', 70000.00);
-- Insert data into CERTIFIED
INSERT INTO CERTIFIED VALUES
(101, 1),
(102, 2),
(103, 3),
(104, 4),
(105, 5);
Q4.Consider the Supply-Parts database given below. The primary keys are underlined. Assume relevant data types for attributes. SUPPLIER (Sid, Sname, Address) PART (PID, Pname, Color) SHIPMENT (Sid, PID, Cost) Create the above tables in SQL. Specify primary and foreign keys properly. Enter at least 5 tuples in each table with relevant data. Solve the following queries. i. Find the Sid’s of suppliers who supply a green part ii. For every supplier print the name of the supplier and the total number of parts that he/she supplies iii. Update the part color supplied by supplier s3 to yellow.
Supply-parts
--Find the Sid’s of suppliers who supply a green part
select * from shipment where pid in (select pid from part where color in ('Green'));
--For every supplier print the name of the supplier and the total number of parts that he/she supplies
select s.sname,(select count(*) from shipment sh where sh.sid=s.sid)as totalparts from supplier s;
-- Update the part color supplied by supplier s3 to yellow
update part set color=('yellow') where pid=(select sh.pid from shipment sh where sh.sid in
(select s.sid from supplier s where sname=('TechSource')));
Q5.Consider the Aircraft database given below. The primary keys are underlined. Assume relevant data types for attributes. AIRCRAFT (Aircraft ID, Aircraft_name, Cruising_range) CERTIFIED (Emp ID, Aircraft ID) EMPLOYEE (Emp ID, Ename, Salary) Create the above tables in SQL. Specify primary and foreign keys properly. Enter at least 5 tuples in each table with relevant data. Solve the following queries. i. Find the names of pilots certified for Boeing aircraft ii. Arrange the Aircrafts with respect to the ascending order of distance. iii. Find the name of pilots who can operate flights with a range greater than 3000 miles but are not certified on any Boeing aircraft.
Aircraft Employee db 2
-- Find the employee ID’s of employee who make the highest salary
select emp_id from employee where salary=(select max(salary) from employee);
--Find the name of aircrafts such that all pilots certified to operate them earn more than 50000
select e.ename from employee e where e.emp_id in
(select c.emp_id from certified c where c.aircraft_id in
(select a.aircraft_id from aircraft a where cruising_range>5000));
--Find the employees who are not certified for operating any aircraft.
select e.ename from employee e where e.emp_id not in (select c.emp_id from certified c);
Hope you learnt something today
Happy Coding…