Scribd
Upload
39 views5 pages

100 Essential SQL Queries Explained

The document contains 100 SQL queries with examples covering various database operations such as creating databases and tables, inserting, updating, and deleting records, as well as performing complex queries and aggregations. It includes commands for managing views, indexes, and transactions, along with examples of using functions and conditions in SQL. The queries are structured to demonstrate practical applications in a school database context.

Uploaded by

dharialokesh11
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
39 views5 pages

100 Essential SQL Queries Explained

The document contains 100 SQL queries with examples covering various database operations such as creating databases and tables, inserting, updating, and deleting records, as well as performing complex queries and aggregations. It includes commands for managing views, indexes, and transactions, along with examples of using functions and conditions in SQL. The queries are structured to demonstrate practical applications in a school database context.

Uploaded by

dharialokesh11
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

100 SQL Queries with Examples

1. CREATE DATABASE school;

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

3. ALTER TABLE students ADD grade CHAR(1);

4. ALTER TABLE students DROP COLUMN grade;

5. DROP TABLE students;

6. TRUNCATE TABLE students;

7. RENAME TABLE students TO pupils;

8. CREATE TABLE courses (course_id INT PRIMARY KEY, title VARCHAR(100));

9. ALTER TABLE courses MODIFY title VARCHAR(200);

10. CREATE TABLE enrollments (student_id INT, course_id INT, PRIMARY KEY(student_id, course_id));

11. INSERT INTO students (id, name, age) VALUES (1, 'Alice', 20);

12. INSERT INTO students (id, name, age) VALUES (2, 'Bob', 22);

13. UPDATE students SET age = 21 WHERE id = 1;

14. DELETE FROM students WHERE id = 2;

15. INSERT INTO courses VALUES (101, 'Mathematics');

16. INSERT INTO enrollments VALUES (1, 101);

17. UPDATE courses SET title = 'Advanced Mathematics' WHERE course_id = 101;

18. DELETE FROM enrollments WHERE student_id = 1;

19. INSERT INTO students VALUES (3, 'Charlie', 23);

20. UPDATE students SET name = 'Alice Smith' WHERE id = 1;

21. SELECT * FROM students;

22. SELECT name FROM students;

23. SELECT id, name FROM students WHERE age > 20;

24. SELECT COUNT(*) FROM students;

25. SELECT AVG(age) FROM students;


26. SELECT MAX(age) FROM students;

27. SELECT MIN(age) FROM students;

28. SELECT DISTINCT age FROM students;

29. SELECT * FROM students ORDER BY age DESC;

30. SELECT * FROM students WHERE name LIKE 'A%';

31. SELECT [Link], [Link] FROM students s JOIN enrollments e ON [Link] = e.student_id JOIN courses c ON

e.course_id = c.course_id;

32. SELECT [Link], [Link] FROM students s LEFT JOIN enrollments e ON [Link] = e.student_id LEFT JOIN courses c

ON e.course_id = c.course_id;

33. SELECT [Link] FROM students s WHERE NOT EXISTS (SELECT * FROM enrollments e WHERE [Link] =

e.student_id);

34. SELECT [Link] FROM students s, enrollments e WHERE [Link] = e.student_id;

35. SELECT [Link], [Link] FROM students s RIGHT JOIN enrollments e ON [Link] = e.student_id RIGHT JOIN courses c

ON e.course_id = c.course_id;

36. SELECT age, COUNT(*) FROM students GROUP BY age;

37. SELECT age, COUNT(*) FROM students GROUP BY age HAVING COUNT(*) > 1;

38. SELECT course_id, COUNT(student_id) FROM enrollments GROUP BY course_id;

39. SELECT course_id, COUNT(*) AS enrollments FROM enrollments GROUP BY course_id HAVING enrollments > 0;

40. SELECT name, age FROM students WHERE age IN (SELECT age FROM students GROUP BY age HAVING

COUNT(*) > 1);

41. SELECT name FROM students WHERE age = (SELECT MAX(age) FROM students);

42. SELECT name FROM students WHERE id IN (SELECT student_id FROM enrollments);

43. SELECT * FROM students WHERE EXISTS (SELECT * FROM enrollments WHERE [Link] =

enrollments.student_id);

44. SELECT course_id FROM enrollments WHERE student_id = (SELECT id FROM students WHERE name = 'Alice');

45. SELECT name FROM students WHERE age > ALL (SELECT age FROM students WHERE age < 22);

46. SELECT name FROM students UNION SELECT title FROM courses;
47. SELECT name FROM students INTERSECT SELECT title FROM courses;

48. SELECT name FROM students EXCEPT SELECT title FROM courses;

49. SELECT id FROM students UNION ALL SELECT student_id FROM enrollments;

50. SELECT name FROM students WHERE age > 21 UNION SELECT name FROM students WHERE age < 19;

51. CREATE VIEW young_students AS SELECT * FROM students WHERE age < 22;

52. SELECT * FROM young_students;

53. DROP VIEW young_students;

54. CREATE VIEW course_summary AS SELECT course_id, COUNT(*) AS total FROM enrollments GROUP BY

course_id;

55. SELECT * FROM course_summary;

56. CREATE INDEX idx_age ON students(age);

57. DROP INDEX idx_age;

58. CREATE UNIQUE INDEX idx_name ON students(name);

59. SHOW INDEX FROM students;

60. CREATE INDEX idx_course_id ON enrollments(course_id);

61. CREATE TABLE teachers (id INT PRIMARY KEY, name VARCHAR(50), subject VARCHAR(50));

62. ALTER TABLE teachers ADD CONSTRAINT unique_name UNIQUE(name);

63. ALTER TABLE students ADD CONSTRAINT fk_id FOREIGN KEY(id) REFERENCES teachers(id);

64. ALTER TABLE teachers DROP CONSTRAINT unique_name;

65. CREATE TABLE admins (id INT CHECK (id > 0), name VARCHAR(100));

66. START TRANSACTION;

67. UPDATE students SET age = age + 1;

68. SAVEPOINT before_update;

69. ROLLBACK TO before_update;

70. COMMIT;

71. SELECT UPPER(name) FROM students;

72. SELECT LOWER(name) FROM students;


73. SELECT LENGTH(name) FROM students;

74. SELECT CONCAT(name, ' - Student') FROM students;

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

76. SELECT CURRENT_DATE;

77. SELECT CURRENT_TIME;

78. SELECT NOW();

79. SELECT DATEDIFF(NOW(), '2023-01-01');

80. SELECT DATE_FORMAT(NOW(), '%Y-%m-%d');

81. SELECT SUM(age) FROM students;

82. SELECT COUNT(DISTINCT age) FROM students;

83. SELECT ROUND(AVG(age), 2) FROM students;

84. SELECT VARIANCE(age) FROM students;

85. SELECT STDDEV(age) FROM students;

86. SELECT name, CASE WHEN age < 21 THEN 'Teen' ELSE 'Adult' END AS category FROM students;

87. SELECT name, IF(age > 22, 'Senior', 'Junior') AS status FROM students;

88. SELECT name, COALESCE(NULL, 'Unknown', name) FROM students;

89. SELECT name FROM students WHERE age BETWEEN 20 AND 25;

90. SELECT name FROM students WHERE age IS NOT NULL;

91. SELECT * FROM students LIMIT 5;

92. SELECT * FROM students OFFSET 3;

93. SELECT * FROM students LIMIT 3 OFFSET 2;

94. SELECT * FROM students WHERE age IN (20, 21, 22);

95. SELECT * FROM students WHERE name NOT LIKE '%a%';

96. GRANT SELECT ON students TO 'user1';

97. REVOKE SELECT ON students FROM 'user1';

98. SELECT name AS student_name FROM students;

99. SELECT [Link], [Link] FROM students s JOIN enrollments e ON [Link] = e.student_id JOIN courses c ON
e.course_id = c.course_id;

100. SELECT COUNT(*) AS total_students FROM students;

Common questions

Powered by AI

Primary keys enforce unique identification of records, while foreign keys maintain referential integrity by linking tables. They ensure valid and related data entries across tables, preventing orphaned records, but may complicate data manipulation due to constraints .

Using the ALTER TABLE statement allows modification of column data types, e.g., ALTER TABLE courses MODIFY title VARCHAR(200). This operation can lead to data truncation if the new type has a smaller size than existing data .

Creating views (e.g., CREATE VIEW young_students AS SELECT * FROM students WHERE age < 22) structures data presentation and simplifies complex queries. Views can enhance performance by optimizing query execution plans but may degrade performance if poorly designed due to additional abstraction layers .

UNION removes duplicate records whereas UNION ALL includes duplicates. UNION may lead to slower performance due to the duplicate removal process, while UNION ALL is faster as it processes records directly. UNION ALL is preferable when all records are needed .

JOIN operations (e.g., INNER, LEFT, RIGHT) allow drawing connections between tables, enriching data queries. For instance, SELECT s.name, c.title FROM students s JOIN enrollments e ON s.id = e.student_id JOIN courses c ON e.course_id = c.course_id. Complexity arises from ensuring correct joins and managing result set duplication or NULL-entry scenarios .

Aggregate functions like AVG() and SUM(), when used with GROUP BY, break data into subsets, applying calculations to each group (e.g., SELECT age, COUNT(*) FROM students GROUP BY age). This facilitates detailed analysis of distinct groups, though grouping can complicate composite analyses .

TRUNCATE TABLE is a high-level, non-transactional operation that quickly removes all rows without logging individual row deletions, resetting identity values, and removing indexes. DELETE FROM table can be transactional, allowing selective row removal with constraints and triggers intact .

Indexes improve query performance for operations like SELECT by reducing data retrieval time. They are particularly beneficial for large datasets with frequent read requires. Limitations include increased storage requirements and slower INSERT/UPDATE operations due to index maintenance .

The CASE statement offers flexible multi-condition logic, handling complex when/then scenarios elegantly within SELECT statements, as seen in SELECT name, CASE WHEN age < 21 THEN 'Teen' ELSE 'Adult' END AS category FROM students. It provides greater readability and reduces code redundancy unlike simple IF conditions .

A RIGHT JOIN retains all records from the right table and matched records from the left table, filling with NULLs as necessary. It is useful when you need all entries from the right table regardless of matches. A LEFT JOIN contrasts this by retaining all left table records .

You might also like