EMP & Dept Table
mysql> use Deep;
Database changed
mysql> Creating the EMP_Deep table
mysql> CREATE TABLE EMP_Deep(
-> EMPNO INT PRIMARY KEY,
NAME VARCHAR(50),
SALARY INT,
DEPTID INT
-> FOREIGN KEY (DEPTID) REFERENCES Dept(DEPTlD));
Query OK, O rows affected (0.10 sec)
mysql> Inserting data into the EMP_Deep table
mysql> INSERT INTO EMP_Deep (EMPNO, NAME, SALARY, DEPTID) VALUES->
(10, 'George', 111400, 10),
> (105, 'Mary', 51600, 20),
(152, 'Sam', 110100, 10),
(215, 'Sarah', 43000, 30),
(244, 'Manila', 15600, 40),
(300, 'Robert', 68200, 30),
(335, 'Henry', 43000, 40), (400, 'Rachel', 115600, 10), (441, 'Peter', 58200, 20);
Query OK, 9 rows affected (0.02 sec)
mysql> Creating the Dept_Deep table
mysql> CREATE TABLE Dept_Deep (
DEPTID INT PRIMARY KEY,
DEPARTMENT VARCHAR(50),
CITY VARCHAR(50)
Query OK, O rows affected (0.04 sec)
mysql> Inserting data into the Dept_Deep table mysql> INSERT INTO Dept_Deep
(DEPTID, DEPARTMENT, CITY) VALUES (10, 'MARKETING', 'NEW DELHI'), (20, 'FINANCE',
'GURUGRAM'), (30, 'SALES', 'NOIDA'),
(40, 'MANUFACTURING', 'SONIPAT');
Query OK, 4 rows affected (0.01 sec)
QI. To display name, salary, city of all employees
mysql> SELECT EMP_Deep NAME, EMP_Deep.SALARY, Dept_Deep.ClTY FROM EMP, dept
Where EMP.DEPTID = Dept.DEPTlD;
Q2. Name of columns which act as primary key and foreign key in tables Emp and Dept
Primary Key: In EMP_Deep table: EMPNO In Dept_Deep table: DEPTID
Foreign Key: In EMP_Deep table: DEPTID (referencing DEPTID in Dept table)
Q3. To display the number of records in each DEPTID.
mysql> SELECT DEPTID,COUNT(*)
FROM EMP Deep
GROUP BY DEPTID;
Q4. To display the TOTAL salary in each DEPTID.
mysql> SELECT DEPTID, SUM(SALARY)
> FROM EMP Deep
GROUP BY DEPTID;
Q5. To display the maximum salary and minimum salary in each department.
mysql> SELECT DEPTID, MAX(SALARY), MIN(SALARY)
FROM EMP Deep
GROUP BY DEPTID;
Student & Stream Table
mysql> use Deepjain
Database changed
mysql> Create the stream_Deep table
mysql> CREATE TABLE stream_Deep
s_lD INT(2) PRIMARY KEY,
Stream CHAR(20),
Location CHAR(50));
Query OK, O rows affected, 1 warning (0.19 sec)
mysql> Create the student_Deep table
mysql> CREATE TABLE student_Deep(
Ad_No INT(2),
First_Name CHAR(50),
Last_Name CHAR(50),
> Class INT(2),
Section CHAR(I),
s_lD INT(2),
->Date_Of_Birth DATE,
Percentage FLOAT (5,2),
FOREIGN KEY (S_ID) REFERENCES stream(S_ID));
Query OK, O rows affected, 4 warnings (0.06 sec) mysql>
— Insert data into the stream Deep table
mysql> INSERT INTO stream_Deep VALUES
(10, 'Science', 'l Block'),
(20, 'Commerce', 'J Block'),
(30, 'Arts', 'Rishi Block');
Query OK, 3 rows affected (0.01 sec)
mysql> Insert data into the student_Deep table
mysql> INSERT INTO student_Deep VALUES
(1, 'SWATHI', 'K', 12, 10, '2007-09-20', 95.50),
(2, 'James', 'Ruber', 12, 'B', 20, '2006-05-20', 65.25),
(3, 'Bharath', 'Kumar', 12, 10, '2006-02-01', 88.50),
(4, 'NAVEEN', 'KUMAR', 12, 'A', 30, '2007-06-10', 50.00),
(5, 'Piyush', 'KUMAR', 12, 'A', 30, '2007-01-10', 46.80),
(6, 'Jatin', 'KUMAR', 12, 'A', 30, '2006-06-08', 56.80),
(7, 'himanshi', 'Panchal', 12, 'A', 30, '2007-06-26', 67.00),
(8, 'Tushar', NULL, 12, 'B', 20, '2008-08-02', 59.80);
Query OK, 8 rows affected (0.01 sec)
mysql> Q2) Select all the elements of the student table
mysql> SELECT* FROM student_Deep; mysql> Q3) Display the
Ad_No, First_Name, Percentage of the students
mysql> SELECT Ad_No, First_Name, Percentage FROM student_Deep;
mysql> Q4) Display the details of all the students with less than 60 percentage
mysql> SELECT* FROM student_Deep WHERE Percentage < 60;
mysql> Q5) Display the Ad_No and First_Name of the students who were born after 01/01/2007
mysql> SELECT Ad_No, First_Name FROM student_Deep WHERE > '2007-01-01';
mysql> Q6) Display the details of the students who were born before 2007
mysql> SELECT* FROM student_Deep WHERE < '2007-01-01';
mysql> Q7) Display the details of all the students whose percentage is NULL
mysql> SELECT* FROM student_Deep WHERE Percentage IS NULL;
mysql> Q8) Display the details of all the students whose percentage is not NULL
mysql> SELECT* FROM student Deep WHERE Percentage IS NOT NULL;
mysql> Q9 & QIO) Display the various sections of the students, each section appearing only once
mysql> SELECT DISTINCT Section FROM student_Deep;
mysql> QII) Display the details of all the students born after 2006 and have a percentage of more
than
50
mysql> SELECT * FROM student_Deep WHERE > '2006-12-31' AND Percentage > 50;
mysql> Q12) Display the First_Name and Percentage of all the students whose section is not 'A'
mysql> SELECT First_Name, Percentage FROM student_Deep
WHERE Section
mysql> — Q13) Display the First_Name and Section of all the students whose percentage is less than
70 or whose S ID is 10
mysql> SELECT First_Name, Section
FROM student Deep
WHERE Percentage < 70 OR s_lD = 10;
mysql> Q14) Display the details of the students who were born in the year 2006
mysql> SELECT *
FROM student Deep
WHERE YEAR(Date_Of_Birth) = 2006;
mysql> — Q15) Display the details of all the students whose percentage is in the range of 50 to 70
mysql> SELECT *
FROM student Deep
WHERE Percentage BETWEEN 50 AND 70;
mysql> — Q16) Display all the details of the students whose DOB is 2007 and class is 11
mysql> SELECT * FROM student_Deep
WHERE YEAR(Date_Of_Birth) = 2007 AND Class = 11
mysql> Q17) Display the First_Name and Class for all the students whose section is 'A' or 'B'
mysql> SELECT First_Name, Class
FROM student Deep
WHERE Section IN ('A', 'B');
mysql> Q18) Display the details of all the students whose percentage is in the range of 50 to 80
mysql> SELECT *
FROM student Deep
WHERE Percentage BETWEEN 50 AND 80;
mysql> Q19) Display the details of all the students who were born between 2006 and 2007 mysql>
SELECT *
FROM student Deep
WHERE Date Of Birth BETWEEN '2006-01-01' AND '2007-12-31';
mysql> Q20) Display the Ad_No, First_Name, and Section of all the students for which the Last_Name
ends with 'Kumar'
mysql>
SELECT Ad_No, First_Name, Section FROM student_Deep WHERE Last_Name LIKE '%Kumar';
mysql> Q21) Display the Ad_No, First_Name, and Section for all the students for which the
First_Name ends with 'a'
mysql> SELECT Ad_No, First_Name, Section FROM student_Deep WHERE First_Name LIKE '%a';
mysql> Q22) Display the Ad_No and First_Name from all the students which contains 'a'
mysql> SELECT Ad_No, First_Name FROM student_Deep WHERE First_Name LIKE '%a%';
mysql> Q23) Display the Ad_No and First_Name from all the students for which the First_Name does
not contain 'e'
mysql> SELECT Ad_No, First_Name FROM student_Deep WHERE First_Name NOT LIKE '%e%';
mysql> Q24) Display the Ad_No and First_Name from all the students for which the First_Name
contains 'a' as the second last character
mysql> SELECT Ad_No, First_Name FROM student_Deep WHERE First_Name LIKE '%a_',
mysql> Q25) Display the details of all the students in ascending order of their Percentage
mysql> SELECT * FROM student_Deep ORDER BY Percentage Asc;
mysql> Q26) Display the details of all the students in descending order of their Date_Of_Birth
mysql> SELECT* FROM student_Deep
ORDER BY Date Of Birth DESC;
mysql>
mysql> Q27) Display the details of all the students in ascending order of their Date_Of_Birth and
Percentage in descending order
SELECT* FROM student_Deep
ORDER BY Date_Of_Birth ASC, Percentage DESC;
mysql> Q28) Change the Percentage of Piyush from 46.80 to 62.80
mysql> UPDATE student_Deep SET Percentage = 62.80 WHERE First_Name = 'Piyush';
mysql> Q29) Increase the percentage by 2 for all the students whose Section is 'C'
mysql> UPDATE student_Deep SET Percentage = Percentage + 2 WHERE Section = 'C';
mysql> — Q30) Delete the records of all the students whose Date_Of_Birth is before
2007
mysql> DELETE FROM student_Deep WHERE < '2007-01-01';
mysql> Q31) Delete all the records of the student named 'Tushar' mysql>
DELETE FROM student Deep WHERE First Name 'Tushar';
mysql> Q32) Add another column Phone_NO of type INT() in the student table
mysql> ALTER TABLE student_Deep ADD Phone_NO INT;
mysql>
Group By Functions
mysql> QI) Display First Name, Class, Section, Location, DOB, and Stream from each student where
stream contains "c" character and DOB is less than 2008.
SELECT First_Name, Class, Section, Location, Date_Of_Birth, Stream
FROM student Deep
JOIN stream ON student Deep.S ID = stream Deep.S ID
WHERE Stream LIKE ‘%c%’AND Date Of Birth < '2008-01-01';
mysql> Q2) Display the number of records in each stream.
mysql> SELECT Stream, COUNT(*)
FROM student Deep
JOIN stream ON student Deep.S ID = stream Deep.S ID
GROUP BY Stream;
mysql> Q3) Display max (Percentage), min (Percentage), count(*), count(Last_Name), avg (Percentage),
sum (Percentage) in each section.
mysql> SELECT Section,
mysql>
-> MAX(Percentage), MIN(Percentage), COUNT(*), COUNT(Last_Name), AVG (Percentage),
SUM(Percentage)
FROM student Deep
GROUP BY Section;
mysql> Q4) Display the number of students in each stream with percentage between 50 to 70.
mysql> SELECT Stream,COUNT(*)
FROM student Deep
JOIN stream ON student Deep.S ID = stream Deep.S ID
WHERE Percentage BETWEEN 50 AND 70
GROUP BY Stream;
mysql> Q5) Display the number of students in each stream where the number is greater than 5.
mysql> SELECT Stream, COUNT(*)
FROM student Deep
JOIN stream ON student Deep.S ID = stream Deep.S ID
GROUP BY Stream
HAVING COUNT(*) > 5;
mysql> Q6) Display the number of students in each stream where percentage is greater than 60 and
the number of streams is greater than 2.
mysql> SELECT Stream, COUNT(*)
FROM student Deep
JOIN stream ON student Deep.S ID = stream Deep.S ID->
WHERE Percentage > 60
GROUP BY Stream
HAVING COUNT(*) > 2;
mysql> Q7) Find the error:
mysql> Corrected query:
mysql> SELECT First_Name, Section, COUNT(*)
FROM student Deep
GROUP BY Section, First Name;
Numeric Functions
mysql> Q8) Round off the percentage to zero decimal places.
mysql> SELECT Ad_No, First_Name, ROUND(Percentage, O) FROM student_Deep;
mysql> Q9) Display 23472.162738 up to 4 decimal places. mysql> SELECT ROUND (23472.162738, 4);
mysql> QIO) Display 23472.162538 up to the nearest ten's place. mysql> SELECT ROUND
(23472.162538, -1);
mysql> QII) Outputs of the following commands:
mysql> 2) SELECT ROUND (23472.162738, 2); - Output: 23472.16
mysql> 1) SELECT ROUND(23472.162738, 3); Output: 23472.163 mysql> 3) SELECT ROUND
(23472.162738, 1); Output: 23472.2 mysql> 4) SELECT ROUND (23472.162738, 0); Output: 23472
mysql> - 5) SELECT ROUND (23472.162738, -1); Output: 23470 mysql> - 6) SELECT ROUND
(23472.162738, -2); Output: 23500 mysql> 7) SELECT ROUND (23472.162738, -3); - Output: 23000
mysql> - 8) SELECT POW(4, -2); Output: 0.0625
mysql> 9) SELECT POW(IO, 2); Output: 100
mysql> 10) SELECT POW(MOD(14, 3), 3); Output: 1
mysql>
Date Functions
Q12) Display day from DOB in student table.
mysql> SELECT Ad_No, First_Name, DAY(Date_Of_Birth) FROM student_Deep;
mysql> Q13) Display year from DOB in student table.
mysql> SELECT Ad_No, First_Name, YEAR(Date_Of_Birth) FROM student_Deep;
mysql> Q14) Display week from DOB in student table. mysql> SELECT Ad_No, First_Name,
WEEK(Date_Of_Birth) FROM student_Deep;
mysql> Q15) Display day name from DOB in student table. mysql> SELECT Ad_No, First_Name,
DAYNAME(Date_Of_Birth) FROM student_Deep; mysql> Q16) Display month from
DOB in student table.
mysql> SELECT Ad_No, First_Name, MONTH(Date_Of_Birth) FROM student_Deep;
mysql> Q17) Display month name from DOB in student table.
mysql>SELECT Ad_No,First_Name ,
MONTHNAME(Date_Of
_Birth) AS Month_Name FROM student Deep;
mysql> Q18) Display the First_Name of the student who was born on "Tuesday".
mysql>
mysql> SELECT Ad_No, First_Name FROM student_Deep WHERE DAYNAME(Date_Of_Birth) = '
Tuesday';
Q19) Display the student information whose DOB is between 2007 and 2008.
mysql> SELECT *
FROM student Deep-> WHERE Date Of Birth BETWEEN '2007-01-01' AND '2008-12- 31';
mysql> Q20) Display the First_Name, Last_Name, and Percentage of the student who was born in
January.
mysql> SELECT First_Name, Last_Name, Percentage FROM student_Deep WHERE
MONTH (Date_Of_Birth) = 1;
mysql> Q21) Display the student information who was born in week 1.
mysql> SELECT * FROM student_Deep WHERE WEEK(Date_Of_Birth)=1;
mysql>
String Function
Q22) Display First_Name in upper case and Last_Name in lower case and merge them.
mysql> SELECT CONCAT(UPPER(First_Name,3),”,LOWER(Last_Name)), FROM student_Deep;
mysql> Q23) Display all characters in First_Name from the 3rd position.
mysql> SELECT SUBSTRING(First_Name, 3), ->FROM student_Deep;
mysql> Q24) Display number of characters in Last_Name where percentage is greater than 70 and
First Name contains "l".
mysql> SELECT LENGTH(Last_Name), ->FROM student_Deep ->WHERE Percentage > 70 AND
First Name LIKE’%I%’;
mysql> Q25) Display first 3 characters from the left of First_Name.
mysql> SELECT LEFT(First_Name,3), FROM student_Deep;
mysql> — Q26) Write the output of the following commands:
mysql> 1) SELECT LENGTH(‘LVM_SCHOOL’);-> Output: 13
mysql> 2) SELECT LENGTH( LTRIM(‘LVM_SCHOOL’));-> Output: 13
rywsql> 3) SELECT LENGTH (RTRIM(‘LVM_SCHOOL”_LVM_School_ ' )); —Output: 13
mysql>
mysql> 4) SELECT LENGTH( TRIM(‘LVM_SCHOOL’)); ->- Output: 13
Q27) Display the position of 'a' substring in First_Name, Last_Name.
mysql> SELECT First_Name, INSTR(First_Name, 'a'), -> Last_Name, INSTR(Last_Name, 'a') AS
Position Last Name -> FROM student Deep;
mysql> Q28) Write the output of the following commands:
mysql> 1) SELECT First_Name, INSTR(First_Name, 'a'); Output: varies by record
mysql> 2) SELECT Last_Name, INSTR(Last_Name, 'a'); Output: varies by record
mysql>
Cartesian Functions
Q29: Cartesian product to display all records of stream and student tables.
mysql> SELECT* FROM stream_Deep, student_Deep;
mysql> Q30: Display the number of columns (Degree) of student, stream, and Cartesian product
tables.
mysql> Display the number of columns (Degree) of student table.
mysql> SELECT COUNT(*)
FROM student Deep;
mysql> Display the number of columns (Degree) of stream table.
mysql> SELECT COUNT(*)
FROM stream Deep • mysql> Display the number of columns (Degree) of the Cartesian product
of stream and student tables.
mysql> SELECT COUNT(*)
FROM stream Deep, student Deep;
mysql> Q31: Display the number of rows (Cardinality) of student, stream, and Cartesian product.
mysql>
Display the number of rows (Cardinality) of the student table.
mysql> SELECT
FROM student Deep;
mysql> Display the number of rows (Cardinality) of the stream table.
mysql> SELECT COUNT(*)
FROM stream Deep;
mysql> Display the number of rows (Cardinality) of the Cartesian product of stream and student
tables.
mysql> SELECT COUNT(*)
FROM stream Deep, student Deep;
Join Function
Q32) Display all the records from stream and student table.
mysql> SELECT * FROM student
WHERE student.S ID= stream.S ID;
mysql>
mysql> Q33) Display student name and their stream (like ART, COMMERCE, SCIENCE) from student
and stream table.
mysql> SELECT First_Name, Last_Name, Stream
-> FROM student
mysql> Q34) Display the number of students in each stream (like ART, COMMERCE, SCIENCE) from
student and stream table.
mysql> SELECT Stream, COUNT(*)
-> FROM student
WHERE student.S ID= stream.S ID
GROUP BY Stream;
mysql> Q35) Display the number of students in each stream having percentage more than 50 and
number of records should be higher than 3 (like ART, COMMERCE, SCIENCE) from student and
stream table.
mysql> SELECT Stream, COUNT(*)AS Num_Students
FROM student
JOIN stream ON student.S ID = stream.S ID
WHERE Percentage > 50
GROUP BY Stream
HAVING COUNT(*)> 3;
