-- ==========================================
-- 1. CREATE DATABASE
-- ==========================================
CREATE DATABASE Students;
-- Use database
USE Students;
-- ==========================================
-- 2. CREATE TABLE
-- ==========================================
CREATE TABLE Academic (
RollNo INT PRIMARY KEY,
Name VARCHAR(100),
Marks INT,
PhoneNo BIGINT
);
CREATE TABLE Sports (
Student_ID INT,
Sports VARCHAR(50),
FOREIGN KEY(Student_ID) REFERENCES Academic(RollNo)
);
-- ==========================================
-- 3. INSERT VALUES
-- ==========================================
INSERT INTO Academic VALUES
(1, 'Ayush', 85, 9876543210),
(2, 'Vipul', 78, 9876543211),
(3, ‘Samarth’, 92, 9876543212),
(4, 'Vishu', 88, 9876543213);
INSERT INTO Sports VALUES
(1, 'Cricket'),
(2, 'Football'),
(3, 'Hockey'),
(4, 'Cricket');
-- ==========================================
-- 4. ALTER TABLE
-- ==========================================
-- Add column
ALTER TABLE Academic
ADD Email VARCHAR(100);
-- Modify column
ALTER TABLE Academic
MODIFY Name VARCHAR(150);
-- Rename column
ALTER TABLE Academic
RENAME COLUMN PhoneNo TO MobileNo;
-- Drop column
ALTER TABLE Academic
DROP COLUMN Email;
-- ==========================================
-- 5. UPDATE
-- ==========================================
UPDATE Academic
SET Marks = 90
WHERE RollNo = 2;
-- ==========================================
-- 6. DELETE
-- ==========================================
DELETE FROM Academic
WHERE RollNo = 4;
-- ==========================================
-- 7. SELECT
-- ==========================================
-- Show all data
SELECT * FROM Academic;
-- Speci c columns
SELECT Name, Marks FROM Academic;
-- Distinct values
SELECT DISTINCT Sports FROM Sports;
fi
-- ==========================================
-- 8. WHERE CONDITIONS
-- ==========================================
SELECT * FROM Academic
WHERE Marks > 80;
SELECT * FROM Academic
WHERE Marks BETWEEN 80 AND 95;
SELECT * FROM Academic
WHERE Name LIKE 'A%';
-- ==========================================
-- 9. ORDER BY
-- ==========================================
SELECT * FROM Academic
ORDER BY Marks DESC;
-- ==========================================
-- 10. AGGREGATE FUNCTIONS
-- ==========================================
SELECT COUNT(*) FROM Academic;
SELECT AVG(Marks) FROM Academic;
SELECT SUM(Marks) FROM Academic;
SELECT MAX(Marks) FROM Academic;
SELECT MIN(Marks) FROM Academic;
-- ==========================================
-- 11. GROUP BY
-- ==========================================
SELECT Sports, COUNT(Student_ID)
FROM Sports
GROUP BY Sports;
-- ==========================================
-- 12. HAVING
-- ==========================================
SELECT Sports, COUNT(Student_ID)
FROM Sports
GROUP BY Sports
HAVING COUNT(Student_ID) > 1;
-- ==========================================
-- 13. JOINS
-- ==========================================
-- INNER JOIN
SELECT [Link], [Link]
FROM Academic
INNER JOIN Sports
ON [Link] = Sports.Student_ID;
-- LEFT JOIN
SELECT [Link], [Link]
FROM Academic
LEFT JOIN Sports
ON [Link] = Sports.Student_ID;
-- RIGHT JOIN
SELECT [Link], [Link]
FROM Academic
RIGHT JOIN Sports
ON [Link] = Sports.Student_ID;
-- FULL OUTER JOIN
SELECT [Link], [Link]
FROM Academic
FULL OUTER JOIN Sports
ON [Link] = Sports.Student_ID;
-- ==========================================
-- 14. SUBQUERY
-- ==========================================
SELECT Name
FROM Academic
WHERE Marks = (
SELECT MAX(Marks)
FROM Academic
);
-- Names of cricket players
SELECT Name
FROM Academic
WHERE RollNo IN (
SELECT Student_ID
FROM Sports
WHERE Sports = 'Cricket'
);
-- ==========================================
-- 15. VIEW
-- ==========================================
CREATE VIEW TopStudents AS
SELECT Name, Marks
FROM Academic
WHERE Marks > 85;
SELECT * FROM TopStudents;
-- ==========================================
-- 16. TRUNCATE
-- ==========================================
TRUNCATE TABLE Sports;
-- ==========================================
-- 17. DROP TABLE
-- ==========================================
DROP TABLE Sports;
-- ==========================================
-- 18. DROP DATABASE
-- ==========================================
DROP DATABASE Students;
-- ==========================================
-- 19. INDEXING
-- ==========================================
CREATE INDEX idx_marks
ON Academic(Marks);
-- Remove index
DROP INDEX idx_marks ON Academic;