CREATE DATABASE student_db;
GO
USE student_db;
GO
-- Create Tablel
CREATE TABLE student_tbl
(
StudentId INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50) ,
Marks INT,
MobileNo VARCHAR(20) ,
Email VARCHAR(50) ,
City VARCHAR(30),
Department VARCHAR(30)
);
-- Insert Muttipte Records (6 Students)
INSERT INTO student_tbl VALUES
(1, 'Ahmed', 'Khan', 85, '0300123456', 'ahmed@[Link]', 'Rawalpindi', 'Cs'),
(2, 'Ali', 'Raza', 92, '0311123456', 'ali@[Link]', 'Lahore', 'IT'),
(3, 'Sara', 'Ahmed', 78, '0322123456', 'sara@[Link]', 'Islamabad', 'SE'),
(4, 'Usman', 'Ali', 88, '0333123456', 'usman@[Link]', 'Karachi', 'CS'),
(5, 'Ayesha', 'Khan', 95, '0344123456', 'ayesha@[Link]', 'Peshawar', 'IT'),
(6, 'Bilal', 'Hassan', 70, '0355123456', 'bilal@[Link]', 'Quetta', 'SE');
-- View All Data
SELECT * FROM student_tbl;
- update Example
UPDATE student_tbl
SET Marks = 90
WHERE StudentId = 3;
-- Detete Example
DELETE FROM student_tbl WHERE StudentId = 6;
-- Maximun Marks
SELECT MAX(Marks) FROM student_tbl;
-- Highest Marks
SELECT TOP 1 Marks
FROM student_tbl
ORDER BY Marks DESC;
-- Second Highest Marks
SELECT TOP 1 Marks
FROM (
SELECT DISTINCT TOP 2 Marks
FROM student_tbl
ORDER BY Marks DESC
) AS Temp
ORDER BY Marks ASC;
-- Minimun Marks
SELECT MIN(Marks) FROM student_tbl;
- - Add CoLumn
ALTER TABLE student_tbl ADD Age INT;
-- Drop Column
ALTER TABLE student_tbl DROP COLUMN Age;
-- Final Data
SELECT * FROM student_tbl;
If You Want To Delete DATABASE You Have to Write
DROP DATABASE student_db;
//* To delete (remove) a column from a table in SQL, you use the
ALTER TABLE ... DROP COLUMN command //….
ALTER TABLE table_name
DROP COLUMN column_name;
Given Below Example of ALTER AND DROP
ALTER TABLE student_tbl
DROP COLUMN Course;
Drop Multiple Columns (MySQL / SQL Server)
ALTER TABLE student_tbl
DROP COLUMN Age, Gender;
Step 1: Add New Column (Course)
ALTER TABLE student_tbl
ADD Course INT;
Step 2: Insert Values (1 to 6)
UPDATE student_tbl SET Course = 1 WHERE StudentId = 1;
UPDATE student_tbl SET Course = 2 WHERE StudentId = 2;
UPDATE student_tbl SET Course = 3 WHERE StudentId = 3;
UPDATE student_tbl SET Course = 4 WHERE StudentId = 4;
UPDATE student_tbl SET Course = 5 WHERE StudentId = 5;
UPDATE student_tbl SET Course = 6 WHERE StudentId = 6;
SELECT * FROM student_tbl;