SQL for Beginners

CREATE DATABASE school;

CREATE TABLE students (id INT, name VARCHAR(50));

INSERT INTO students VALUES (1, "Amit");

SELECT * FROM students;

SELECT * FROM students WHERE id=1;

UPDATE students SET name="Kapil" WHERE id=1;

DELETE FROM students WHERE id=1;

SELECT * FROM students INNER JOIN marks ON students.id=marks.stu_id;

SELECT AVG(marks) FROM marks;

SELECT DISTINCT name FROM students;

SELECT * FROM students ORDER BY name ASC;

SELECT * FROM students LIMIT 5;

SELECT * FROM students WHERE name LIKE "A%";

SELECT * FROM students WHERE id IN (1,2,3);

SELECT * FROM students WHERE id BETWEEN 1 AND 5;

SELECT * FROM students WHERE name IS NOT NULL;

SELECT name AS student_name FROM students;

SELECT s.name FROM students AS s;

SELECT * FROM students s INNER JOIN marks m ON s.id=m.stu_id;

SELECT * FROM students s LEFT JOIN marks m ON s.id=m.stu_id;

SELECT * FROM students s RIGHT JOIN marks m ON s.id=m.stu_id;

SELECT * FROM students s FULL OUTER JOIN marks m ON s.id=m.stu_id;

SELECT a.name, b.name FROM employees a, employees b WHERE a.manager_id=b.id;

SELECT * FROM students CROSS JOIN marks;

SELECT stu_id, COUNT(*) FROM marks GROUP BY stu_id;

SELECT stu_id, AVG(marks) FROM marks GROUP BY stu_id HAVING AVG(marks) > 50;

SELECT SUM(marks) FROM marks;

SELECT COUNT(*) FROM students;

SELECT AVG(marks) FROM marks;

SELECT MIN(marks), MAX(marks) FROM marks;

SELECT CONCAT(name," Kumar") FROM students;

SELECT SUBSTRING(name,1,3) FROM students;

SELECT TRIM("   Kapil   ");

SELECT UPPER(name), LOWER(name) FROM students;

SELECT REPLACE(name,"Kapil","Amit") FROM students;

SELECT NOW(), CURDATE();

SELECT DATEDIFF("2025-10-05","2025-10-01");

SELECT DATE_ADD(CURDATE(), INTERVAL 5 DAY);

SELECT name, CASE WHEN marks>50 THEN "Pass" ELSE "Fail" END FROM marks;

SELECT name FROM students UNION SELECT name FROM teachers;

SELECT name FROM students UNION ALL SELECT name FROM teachers;

SELECT * FROM students s WHERE EXISTS (SELECT * FROM marks m WHERE m.stu_id=s.id);

SELECT * FROM students s WHERE NOT EXISTS (SELECT * FROM marks m WHERE m.stu_id=s.id);

SELECT * FROM students WHERE id IN (SELECT stu_id FROM marks);

SELECT * FROM students WHERE id NOT IN (SELECT stu_id FROM marks);

SELECT name FROM students WHERE id=(SELECT MAX(stu_id) FROM marks);

CREATE INDEX idx_name ON students(name);

ALTER TABLE students ADD PRIMARY KEY(id);

ALTER TABLE marks ADD FOREIGN KEY (stu_id) REFERENCES students(id);

CREATE TABLE students (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50));

CREATE TABLE students (id INT PRIMARY KEY, name VARCHAR(50) NOT NULL, age INT CHECK(age>0));

CREATE VIEW student_marks AS SELECT s.name, m.marks FROM students s JOIN marks m ON s.id=m.stu_id;

DROP VIEW student_marks;

TRUNCATE TABLE students;

DROP TABLE students;

ALTER TABLE students RENAME TO student_info;

ALTER TABLE students RENAME COLUMN name TO student_name;

ALTER TABLE students ADD COLUMN age INT;

ALTER TABLE students DROP COLUMN age;

ALTER TABLE students MODIFY COLUMN name VARCHAR(100);

START TRANSACTION;
UPDATE students SET name="Kapil" WHERE id=1;
ROLLBACK;

START TRANSACTION;
UPDATE students SET name="Kapil" WHERE id=1;
COMMIT;

SAVEPOINT sp1;
UPDATE students SET name="Amit" WHERE id=1;
ROLLBACK TO sp1;

EXPLAIN SELECT * FROM students;

SELECT COUNT(*) AS total_students FROM students;

SELECT COUNT(DISTINCT name) FROM students;

SELECT s.name, m.marks, c.name FROM students s JOIN marks m ON s.id=m.stu_id JOIN courses c ON m.course_id=c.id;

SELECT name, (SELECT AVG(marks) FROM marks WHERE stu_id=students.id) AS avg_marks FROM students;

SELECT name FROM students s WHERE EXISTS (SELECT * FROM marks m WHERE m.stu_id=s.id AND m.marks>50);

UPDATE students SET name="Anonymous" WHERE id>5;

UPDATE students SET name=CASE WHEN id=1 THEN "Kapil" ELSE "Amit" END;

DELETE FROM students ORDER BY id DESC LIMIT 1;

UPDATE students SET name="Test" ORDER BY id ASC LIMIT 1;

-- This is a comment
SELECT * FROM students;

/* Comment line 1
Comment line 2 */
SELECT * FROM students;

CREATE TEMPORARY TABLE temp_students (id INT, name VARCHAR(50));

SELECT IFNULL(name,"No Name") FROM students;

SELECT COUNT(CASE WHEN marks>50 THEN 1 END) AS passed_students FROM marks;

CREATE PROCEDURE get_students() BEGIN SELECT * FROM students; END;

CALL get_students();

CREATE FUNCTION total_marks(stu INT) RETURNS INT BEGIN DECLARE t INT; SELECT SUM(marks) INTO t FROM marks WHERE stu_id=stu; RETURN t; END;

CREATE TRIGGER before_insert_student BEFORE INSERT ON students FOR EACH ROW SET NEW.name=UPPER(NEW.name);

START TRANSACTION; SAVEPOINT sp1; UPDATE students SET name="A"; ROLLBACK TO sp1;

CREATE VIEW student_info AS SELECT s.name, m.marks FROM students s JOIN marks m ON s.id=m.stu_id;

GRANT SELECT, INSERT ON school.* TO "user"@"localhost";

REVOKE INSERT ON school.* FROM "user"@"localhost";

-- Backup
mysqldump -u root -p school > school.sql
-- Restore
mysql -u root -p school < school.sql

LOAD DATA INFILE "students.csv" INTO TABLE students FIELDS TERMINATED BY "," LINES TERMINATED BY "\n";

SELECT * FROM students INTO OUTFILE "students.csv" FIELDS TERMINATED BY "," LINES TERMINATED BY "\n";