SQL Reminder Jiankang Yuan Martin Lemke SQL Reminder

SQL Reminder Jiankang Yuan Martin Lemke

SQL Reminder - SELECT column_name 1, column_name 2, … FROM table_name SELECT * FROM table_name

SQL Reminder - SELECT Example ID Name Project Semester 1 Peter Geda 5 2 Paul Eco System 9 3 Mary Geda 7 Table ‚Software_Project‘ Query SELECT Name, Semester FROM Software_Project Result: Name Semester Peter 5 Paul 9 Mary 7

SQL Reminder - WHERE SELECT column_name 1, column_name 2, … FROM table_name WHERE column_name. X operator value Example Query SELECT Name, Semester FROM Software_Project WHERE Semester > 5 Result: Name Semeste r Paul 9 Mary 7

SQL Reminder – Aggregate Functions AVG() - Returns the average value COUNT() - Returns the number of rows FIRST() - Returns the first value LAST() - Returns the last value MAX() - Returns the largest value MIN() - Returns the smallest value SUM() - Returns the sum etc.

SQL Reminder – GROUP BY SELECT column_name, aggregate_function(column_name) FROM table_name WHERE column_name operator value GROUP BY column_name Example Query SELECT Project, COUNT(Name) FROM Software_Project WHERE Semester > 4 GROUP BY Project Result: Project COUNT(Nam e) Geda 2 Eco System 1

SQL Reminder - HAVING SELECT column_name, aggregate_function(column_name) FROM table_name WHERE column_name operator value GROUP BY column_name HAVING aggregate_function(column_name) operator value Example Query SELECT Project, COUNT(Name) FROM Software_Project WHERE Semester > 4 GROUP BY Project HAVING COUNT(Name) > 1 Result: Project COUNT(Name) Geda 2

SQL Reminder – AND, ORDER BY AND & OR Use AND and OR to join more conditions in the WHERE or HAVING clause ORDER BY SELECT column_name(s) FROM table_name ORDER BY column_name(s) ASC|DESC

SQL Reminder – CREATE TABLE table_name ( column_name 1 data_type, column_name 2 data_type, column_name 3 data_type, . . ) Example CREATE TABLE seminar ( ID int, Name varchar(255), Topic varchar(255) ) Result ID Name Topic

SQL Reminder – INSERT INTO table_name VALUES (value 1, value 2, value 3, . . . ) INSERT INTO table_name (column 1, column 2, column 3, . . . ) VALUES (value 1, value 2, value 3, . . . ) Example Query INSERT INTO Software_Project VALUES (4, John, News Analysis, 5) Result: ID Name Project Semester 1 Peter Geda 5 2 Paul Eco System 9 3 Mary Geda 7 4 John News Analysis 5

SQL Reminder - UPDATE table_name SET column 1=value, column 2=value 2, . . . WHERE some_column=some_value Example Query UPDATE Software_Project SET Semester = 17 WHERE Name = ‚John‘ Result: ID Name Project Semester 1 Peter Geda 5 2 Paul Eco System 9 3 Mary Geda 7 4 John News Analysis 17

SQL Reminder - DELETE FROM table_name WHERE some_column=some_value Example Query DELETE FROM Software_Project WHERE Project = ‚News Analysis‘ Result: ID Name Project Semester 1 Peter Geda 5 2 Paul Eco System 9 3 Mary Geda 7

SQL Reminder – CREATE INDEX index_name ON table_name (column_name) • Indexes can speed up queries • Useful for joining tables: index fields involved in a join • Primary keys automatically set an index