NAME - DEVRAJ KUSHWAHA CLASS - 12-B
✅ Step 1: Create the Stream Table
CREATE TABLE Stream (
Stream_ID INT PRIMARY KEY AUTO_INCREMENT,
Stream_Name VARCHAR(50) NOT NULL,
Student_Location VARCHAR(100)
);
✅ Step 2: Create the Student Table
CREATE TABLE Student (
Student_ID INT PRIMARY KEY AUTO_INCREMENT,
First_Name VARCHAR(50),
Last_Name VARCHAR(50),
Student_Name VARCHAR(100),
Class VARCHAR(10),
Section VARCHAR(10),
Stream VARCHAR(50),
Percentage DECIMAL(5,2),
Location VARCHAR(100),
DOB DATE
);
✅ Step 3: Insert Sample Data into Stream Table
INSERT INTO Stream (Stream_Name, Student_Location) VALUES
('Science', 'Delhi'),
('Commerce', 'Mumbai'),
('Arts', 'Kolkata'),
('ComputerScience', 'Bangalore');
✅ Step 4: Insert Sample Data into Student Table
INSERT INTO Student (First_Name, Last_Name, Student_Name, Class, Section,
Stream, Percentage, Location, DOB) VALUES
('Amit', 'Verma', 'Amit Verma', '12', 'A', 'Science', 88.5, 'Delhi', '2007-03-15'),
('Priya', 'Sharma', 'Priya Sharma', '12', 'B', 'Commerce', 72.4, 'Mumbai', '2006-11-23'),
('Vikram', 'Singh', 'Vikram Singh', '11', 'C', 'Arts', 65.0, 'Kolkata', '2008-01-12'),
('Neha', 'Gupta', 'Neha Gupta', '12', 'A', 'ComputerScience', 59.0, 'Bangalore',
'2009-05-30'),
('Ishaan', 'Mehra', 'Ishaan Mehra', '10', 'B', 'Science', 92.3, 'Delhi', '2007-07-07'),
('Ritika', 'Kapoor', 'Ritika Kapoor', '11', 'C', 'Commerce', 48.5, 'Mumbai', '2008-10-10'),
('Anjali', 'Kumar', 'Anjali Kumar', '12', 'A', 'Arts', 71.6, 'Kolkata', '2007-01-01'),
('Rahul', 'Chawla', 'Rahul Chawla', '10', 'B', 'ComputerScience', 66.7, 'Bangalore',
'2006-12-21'),
('Deepa', 'Seth', 'Deepa Seth', '12', 'C', 'Science', 58.9, 'Delhi', '2008-08-08');
Group By Command
Q1
SELECT s.Student_Name, s.Student_Stream, st.Student_Location
FROM Student s
JOIN Stream st ON s.Student_Stream = st.Stream_Name;
Q2 Q2) WAP to display student ID , First Name and stream where percentage > 60 .
SELECT Student_ID, First_Name, Stream
FROM Student
WHERE Percentage > 60;
Q3
SELECT First_Name, Class, Section, Location, DOB, Stream
FROM Student
WHERE Stream LIKE '%c%' AND DOB < '2008-01-01';
Q4
SELECT Stream, COUNT(*) AS No_of_Records
FROM Student
GROUP BY Stream;
Q5
SELECT Section, MAX(Percentage) AS Max_Percentage, MIN(Percentage) AS
Min_Percentage
FROM Student
GROUP BY Section;
Q6
SELECT Stream, COUNT(*) AS No_of_Students
FROM Student
GROUP BY Stream;
Q7
SELECT Stream, COUNT(*) AS No_of_Students
FROM Student
GROUP BY Stream
HAVING COUNT(*) > 5;
Q8
SELECT Stream, COUNT(*) AS No_of_Students
FROM Student
WHERE Percentage > 60
GROUP BY Stream
HAVING COUNT(*) > 2;
Q9 (Error Fixed)
SELECT Section, COUNT(*) AS Count_Students
FROM Student
GROUP BY Section;
Math Command
Q10
SELECT ROUND(Percentage, 0) AS Rounded_Percentage
FROM Student;
Q11
SELECT ROUND(23472.162738, 4);
Q12
SELECT ROUND(23472.162738, -1);
Q13
SELECT ROUND(23472.162738, 3);
SELECT ROUND(23472.162738, 2);
SELECT ROUND(23472.162738, 1);
SELECT ROUND(23472.162738, 0);
SELECT ROUND(23472.162738, -1);
SELECT ROUND(23472.162738, -2);
SELECT ROUND(23472.162738, -3);
SELECT POW(4, -2);
SELECT POW(10, 2);
SELECT POW(MOD(14, 3), 3);
Date Function
Q14
SELECT DAY(DOB) AS Day_Of_Birth FROM Student;
Q15
SELECT YEAR(DOB) AS Birth_Year FROM Student;
Q16
SELECT WEEK(DOB) AS Birth_Week FROM Student;
Q17
SELECT DAYNAME(DOB) AS Day_Name FROM Student;
Q18
SELECT MONTH(DOB) AS Month FROM Student;
Q19
SELECT MONTHNAME(DOB) AS Month_Name FROM Student;
MYSQL Ques
Q20
SELECT First_Name
FROM Student
WHERE DAYNAME(DOB) = 'Tuesday';
Q21
SELECT *
FROM Student
WHERE YEAR(DOB) BETWEEN 2007 AND 2008;
Q22
SELECT First_Name, Last_Name, Percentage
FROM Student
WHERE MONTH(DOB) = 1;
Q23
SELECT *
FROM Student
WHERE WEEK(DOB) = 1;
String Function
Q24
SELECT CONCAT(UPPER(First_Name), LOWER(Last_Name)) AS Merged_Name
FROM Student;
Q25
SELECT SUBSTRING(First_Name, 3)
FROM Student;
Q26
SELECT LENGTH(Last_Name) AS Name_Length
FROM Student
WHERE Percentage > 70 AND First_Name LIKE '%I%';
Q27
SELECT LEFT(First_Name, 3)
FROM Student;
Q28
SELECT LENGTH("__LVM_School_");
SELECT LENGTH(LTRIM("__LVM_School_"));
SELECT LENGTH(RTRIM("__LVM_School_"));
SELECT LENGTH(TRIM("__LVM_School_"));
Q29
SELECT LOCATE('a', First_Name) AS Pos_First, LOCATE('a', Last_Name) AS Pos_Last
FROM Student;
Q30
SELECT First_Name, INSTR(First_Name, 'a')
FROM Student;
SELECT Last_Name, INSTR(Last_Name, 'a')
FROM Student;
Cartesian Product
Q31
SELECT *
FROM Student
CROSS JOIN Stream;
Q32
SELECT COUNT(*) AS No_of_Columns
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME IN ('Student', 'Stream');
Q33
SELECT COUNT(*) AS No_of_Rows
FROM Student
CROSS JOIN Stream;