1) CREATE DATABASE databasename;
CREATE DATABASE testDB;
create database industryDB;
2)
CREATE TABLE Student
(
Roll int(5),
Name varchar(20),
Gender varchar(20),
Age int(5),
GPA double(3,2),
City varchar(20),
Postalcode int(20),
Country varchar(20),
PRIMARY KEY(Roll)
);
CREATE TABLE persons
(
PersonsID int(6),
FirstName varchar(30),
Lastname varchar(30),
Address varchar(30),
City varchar(30),
PRIMARY KEY(PersonsID)
);
CREATE TABLE teacher
(
ID int NOT NULL AUTO_INCREMENT,
Name varchar(20) NOT NULL,
Department varchar(15),
Salary double(10,2),
PRIMARY KEY(ID)
);
CREATE TABLE examresult
(
Reg_No int(6) NOT NULL,
Roll int(6) NOT NULL,
GPA double(1,2),
Course varchar(6),
PRIMARY KEY(Roll)
);
create table employee
(
eID int not null,
ename varchar(15),
street varchar(10),
city varchar(10),
age int,
primary key(eID)
);
The UNIQUE constraint ensures that all values in a column are different.
Both the UNIQUE and PRIMARY KEY constraints provide a guarantee for
uniqueness for a column or set of columns.
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
UNIQUE (ID)
);
The "PersonID" column in the "Persons" table is the PRIMARY KEY in the
"Persons" table.
The "PersonID" column in the "Orders" table is a FOREIGN KEY in the "Orders"
table.
CREATE TABLE Orders (
OrderID int NOT NULL,
OrderNumber int NOT NULL,
PersonID int,
PRIMARY KEY (OrderID),
FOREIGN KEY (PersonID) REFERENCES Persons(PersonID)
);
create table works
(
ename varchar(10),
cname varchar(10),
salary int,
jDate date
);
create table company
(
cname varchar(15),
city varchar(12)
);
create table manages
(
ename varchar(15),
mname varchar(15)
);
3)
RENAME TABLE Student to Student_details;
4)
Show databases;
5)
DROP TABLE student_details;
6)
INSERT INTO student_details
(Roll,Name,Gender,Age,GPA,City,Postalcode,Country)
VALUES
(101,'Rahim','Male',20,3.26,'Kittonkula',2102,'Dhaka'),
(102,'Selim','Male',25,3.56,'Badda',2489,'Cummila'),
(103,'Jorna','Female',18,3.01,'Bari',1301,'Barisal'),
(104,'Rahima','Female',25,2.26,'Jongal',3210,'Kulna'),
(105,'Karim','Male',30,3.76,'Savat',4301,'Rangpur'),
(106,'Atiq','Male',43,2.50,'Bay',5632,'Sylet'),
(107,'Rahim','Male',20,3.65,'Sankipara',7654,'Mymensing'),
(108,'Rahim','Male',19,3.70,'Rajna',8764,'Rajshahi'),
(109,'Selina','Female',31,3.39,'Sodor',9201,'Cummila');
INSERT INTO teacher(Name,Department,Salary)
Values
('Ping','CSE',2300000.500),
('Jing','EEE',300000.500),
('Xuang','ETE',357000.750),
('Kuang','Antro',750000.34),
('Klama','MME',3900000.50),
('Sahang','Civil',900000.50);
INSERT INTO teacher(Name, Department,Salary)
VALUES
('Karim','English',35000),
('Rafe','Bangla',39000),
('Kamrul','Math',4500.45),
('Shahadat','Pharmacy',50000.35);
INSERT INTO examresult(Reg_No, Roll, GPA, Course)
VALUES
(37001, 104, 3.26, 'Science'),
(37002, 105, 3.56, 'Arts'),
(37006, 109, 3.01, 'Commerce'),
(37007, 106, 3.70, 'Science');
insert into employee (eID, ename, street,city,age)
values
(101,'Raf','joomla','jamica',18),
(102,'kaf','jamica','cardif',19),
(103,'john','jam','netif',20),
(104,'kaif','kuds','jersy',17),
(105,'neela','kandi','kendua',21),
(106,'rumpa','jigatola','dhaka',27);
insert into works(ename, cname, salary, jDate)
values
('Raf','text',20000,'2001-2-2'),
('kaf','akij',25000,'2002-3-4'),
('neela','pran',17000,'2008-5-1'),
('john','rfl',18000,'2010-6-2'),
('kaif','rfl',19000,'2007-6-1'),
('rumpa','pran',16000,'2010-1-1');
insert into company (cname,city)
values
('text','cardif'),
('akij','dhaka'),
('pran','kendua'),
('rfl','kulna');
insert into manages(ename,mname)
values
('Raf','rahman'),
('kaf','jasim'),
('john','mike'),
('kaif','don'),
('neela','della'),
('rumpa','dolla');
7)
DROP TABLE student_details;
8)
SELECT Roll, Name, City
FROM student_details;
SELECT * FROM student_details;[* means all field]
SELECT DISTINCT Country
FROM student_details;
a) SELECT COUNT(DISTINCT Country) FROM Customers;
b) SELECT Count(*) AS DistinctCountries
FROM (SELECT DISTINCT Country FROM Customers);
SELECT *
FROM student_details
LIMIT 4;
SELECT *
FROM student_details
LIMIT 2,5;[1st two numbers omit, next 5 numbers display]
SELECT TOP 3 * FROM Customers;[1st 3 records] equivalent to
SELECT * FROM Customers
LIMIT 3; Equivalent to
SELECT * FROM Customers
FETCH FIRST 3 ROWS ONLY;
9)
SELECT Name
FROM student_details
ORDER BY Name;
SELECT Name, Age, City
FROM student_details
ORDER BY Name; [default ASC]
SELECT Roll, Age, City
FROM student_details
ORDER BY Roll DESC;
SELECT Roll, Age, GPA
FROM student_details
ORDER BY Roll, GPA DESC;
SELECT * FROM Customers
ORDER BY Country ASC, CustomerName DESC;
10)
SELECT 2+5;
SELECT 5-2;
SELECT 5*2;
SELECT 10/3;
SELECT 10%3;
11)
SELECT City
FROM student_details
WHERE Gender='Female';
SELECT DISTINCT City
FROM student_details
WHERE Gender='Female';
SELECT DISTINCT City
FROM student_details
WHERE Gender='Female'
ORDER BY City DESC;
SELECT *
FROM student_details
WHERE Age = 18;
SELECT *
FROM `student_details`
WHERE Age<=23;
SELECT *
FROM student_details
WHERE Age>=25;
SELECT *
FROM student_details
WHERE Roll = 105;
SELECT Roll, Name, GPA
FROM student_details
WHERE Roll BETWEEN 102 AND 105;
<> means Not equal.
Note: In some versions of SQL this operator may be written as !=
SELECT * FROM student_details
Where Roll <> 102;
SELECT Roll, Name, GPA
FROM student_details
WHERE Roll != 102;
SELECT *
FROM student_details
WHERE Gender='Male' OR GPA>=3.5;
SELECT *
FROM student_details
WHERE Gender='Male' AND GPA>=3.5;
SELECT *
FROM student_details
WHERE Country='Dhaka'
AND
(Gender='Male' OR GPA >= 3.5);
SELECT CustomerName, ContactName, Address
FROM Customers
WHERE Address IS NULL;
12)
SELECT *
FROM student_details
WHERE Country IN ('Mymensingh','Sylet','Barisal');
[we can use ‘IN’ instead of ‘OR’]
SELECT *
FROM student_details
WHERE Country NOT IN ('Mymensing','Sylet','Barisal');
13)
The percent sign (%) represents zero, one, or multiple characters
The underscore sign (_) represents one, single character
SELECT *
FROM student_details
WHERE Name LIKE 's%'; [Name start with ‘s’ character]
SELECT *
FROM student_details
WHERE Name LIKE '%a' [Name last character will be ‘a’]
SELECT *
FROM student_details
WHERE Name LIKE '%ai%';[‘ai’ character will be in name in any position]
SELECT *
FROM student_details
WHERE Name LIKE '%hi%';
SELECT *
FROM student_details
WHERE Name LIKE 'a__%';
[Finds any values that start with "a" and are at least 2 characters in
length]
SELECT *
FROM student_details
WHERE Name LIKE 'a___%';
[Finds any values that start with "a" and are at least 3 characters in
length]
SELECT *
FROM student_details
WHERE Name LIKE '%n_';
SELECT * FROM Customers
WHERE ContactName LIKE 'a%o';
[ Finds any values that start with "a" and ends with "o"]
14)
SELECT Roll AS ID, Name AS 'Student Name'
FROM student_details;
15)
UPDATE teacher
SET Department='Textile'
WHERE ID = 4;
UPDATE teacher
SET Salary= Salary + 10000
WHERE Salary > 30000;
N.B: Be careful when updating records. If you omit the WHERE clause,
ALL records will be updated!
UPDATE Customers
SET ContactName='Juan';
16)
DELETE FROM teacher
WHERE ID=4;
DELETE FROM Customers;
17)
SELECT UPPER (‘I am Rafe’);
SELECT LOWER('I AM RAfe');
SELECT UPPER(Country)
FROM student_details;
SELECT CONCAT('Welcome', 'to Database site');
SELECT CONCAT(Name,' is ',Age,' years old')
FROM student_details;
SELECT GREATEST(12,-5,100,56,99,45);
SELECT LEAST(12,-5,100,56,99,45);
SELECT POW(5,3);
SELECT LOG(3);
SELECT LOG10(3);
SELECT TRUNCATE(LOG(2),4);
SELECT RAND();
SELECT TOP 3 * FROM Customers;
18)
DESCRIBE student_details;
DESCRIBE teacher;
19)
SELECT COUNT(*)
FROM student_details;
SELECT MAX(GPA)
FROM student_details;
SELECT MIN(GPA)
FROM student_details;
SELECT AVG(Price)
FROM Products;
SELECT SUM(Salary), AVG(Salary)
FROM teacher;
SELECT COUNT(*), MAX(Salary), MIN(Salary), SUM(Salary), AVG(Salary)
FROM teacher;
SELECT Name,Roll,Age, MIN(GPA)
FROM student_details
WHERE Gender='Male';
20)
SELECT *
FROM teacher
WHERE Salary > (SELECT AVG(Salary) FROM teacher);
21)
ALTER TABLE teacher
ADD Age int(5);
ALTER TABLE teacher
ADD City varchar(15);
ALTER TABLE teacher
CHANGE City Country varchar(15);
ALTER TABLE teacher
DROP COLUMN Country;
22)
SELECT Department, SUM(Salary)
FROM teacher
GROUP BY Department;
SELECT Department, SUM(Salary)
FROM teacher
GROUP BY Department
ORDER BY SUM(Salary);
SELECT Department, SUM(Salary)
FROM teacher
GROUP BY Department
ORDER BY SUM(Salary) DESC;
Note: The HAVING clause was added to SQL because the WHERE keyword
cannot be used with aggregate functions.
SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
HAVING COUNT(CustomerID) > 5;
SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
HAVING COUNT(CustomerID) > 5
ORDER BY COUNT(CustomerID) DESC;
23)
TRUNCATE TABLE student_details;
SQL INDEX:
CREATE INDEX index_name
ON table_name (column1, column2, ...);
CREATE UNIQUE INDEX index_name
ON table_name (column1, column2, ...);
CREATE INDEX idx_lastname
ON Persons (LastName);
CREATE INDEX idx_pname
ON Persons (LastName, FirstName);
SQL INJECTION:
SELECT * FROM Users WHERE UserId = 105 OR 1=1;
SELECT UserId, Name, Password FROM Users WHERE UserId = 105 or 1=1;
24)
SELECT [Link], exam.Reg_No, [Link], [Link], [Link],
[Link]
FROM student_details AS std, examresult AS exam
WHERE [Link] = [Link]
25)
SELECT [Link], exam.Reg_No, [Link], [Link], [Link],
[Link]
FROM student_details AS std JOIN examresult AS exam
ON [Link] = [Link]
26)
SELECT [Link], exam.Reg_No, [Link], [Link], [Link],
[Link]
FROM student_details AS std INNER JOIN examresult AS exam
ON [Link] = [Link];
27)
SELECT [Link], exam.Reg_No, [Link], [Link], [Link],
[Link]
FROM student_details AS std LEFT JOIN examresult AS exam
ON [Link] = [Link];
28)
SELECT [Link], exam.Reg_No, [Link], [Link], [Link],
[Link]
FROM student_details AS std RIGHT JOIN examresult AS exam
ON [Link] = [Link];
SELF JOIN:
Study table(S_ID,C_ID,Since) here C_ID Primary key and S_ID Foreign
key
Find Student id who is enrolled in at least two courses.
JOIN = Cross product + some condition.
Select T1.S_ID
FROM Study as T1, Study as T2
Where T1.S_ID= T2.S_ID AND T1.C_ID <> T2.C_ID;
SELECT [Link] AS CustomerName1,
[Link] AS CustomerName2, [Link]
FROM Customers A, Customers B
WHERE [Link] <> [Link]
AND [Link] = [Link]
ORDER BY [Link];
29)
SELECT Roll,Name,Gender
FROM student_details
UNION
SELECT Roll,Name,Gender
FROM exam; [Sequence must be same,Duplicate value will be removed]
30)
SELECT Roll,Name,Gender
FROM student_details
UNION ALL
SELECT Roll,Name,Gender
FROM exam;[Duplicate will not be removed]
select eID, ename, city
from employee;
31)
CREATE VIEW student_view AS
SELECT Roll,Name,Gender
FROM student_details;
SELECT *
FROM student_view;
UPDATE student_view
SET Name= 'Fahim'
WHERE Roll=105;
32)
SELECT CURTIME();
SELECT NOW();
SELECT ADDDATE('2020-12-4', INTERVAL 5 DAY);
SELECT SUBDATE('2020-12-4', INTERVAL 5 DAY);
SELECT MAKEDATE(2020,365);
SELECT DAYNAME('2020-12-4');
SELECT MONTHNAME('2020-12-4');
select * from employee;
select distinct cname
from company;
Question-1: Find the names, street address, and cities of residence for all employees who
work for 'pran' and earn more than $16,000.
select [Link], [Link], [Link]
from employee e, works w
where [Link] = [Link]
and ([Link]='pran' and [Link] > 16000);
Question-2: Find the names of all employees in the database who live in the same cities as the
companies for which they work.
select [Link]
from employee e, works w, company c
where ([Link] = [Link] and [Link] = [Link] and [Link] = [Link]);
Question-3: Find the names of all employees in the database who live in the same cities and
on the same streets as do their managers.
select [Link]
from employee p, employee r, manages m
where [Link] = [Link] and [Link] =[Link] and [Link] = [Link] and [Link] = [Link];
result: empty;
a.
b.
c. Find the names of all employees who work for Pran.
Select *ename
from works
where cname = 'pran';
