SQL Basics Flashcards
Creating Tables
CREATE TABLE Students(name VARCHAR(20), identification INT)
Constraints
CONSTRAINT PRIMARY KEY (identification)
Dropping Tables
DROP TABLE Students;
Inserting into tables
INSERT INTO Students(name, identification) VALUES (‘Jake’, 204902);
Selecting Values
SELECT * FROM Students;
Modifying Tables
ALTER TABLE Students ADD email VARCHAR(100);
ALTER TABLE Students MODIFY email VARCHAR(200);
Deleting Values
DELETE FROM Students WHERE name=’Jake’
Updating Values
UPDATE Students SET identification=20139 WHERE name=’Jake’
Conditions
- NOT -> use everything except what is specified
- BETWEEN -> between integers (10 and 20 etc)
- LIKE -> for string matching
Projection
Allows you to choose what to select (SELECT * ,SELECT Events etc. You could have SELECT Grades, Name, aka multiple).
Distinct
Removes duplicate rows (SELECT DISTINCT.
Renaming
Allows you to rename attributes (SELECT Event AS Activity, SELECT first_name, family_name AS Surname)
New Columns
Using math and AS to create new columns (SELECT Price * Number AS Total_cost).
Cross Product
In a way, its joining tables (SELECT first_name, grade FROM Students, Results).
Natural Join
Another way of doing it (SELECT * FROM Employees NATURAL JOIN Transactions)
Removing Duplicates
DISTINCT
WHERE Statements (WHERE IN, WHERE EXISTS)
SELECT * FROM Students WHERE name IN(‘John’, ‘Sam’). You could also use IN(SELECT name FROM Lecturers) instead of having to provide all the names.
SELECT * FROM Students WHERE EXISTS (SELECT 1 FROM Lecturers WHERE Students.name=Lecturers.name AND Students.last_name=Lecturers.last_name). A generalisation of IN and is used to find multiple.
Grouping
- Shows us how many things are connected to what is specified (SELECT id, count FROM Transactions GROUP BY id - this would show the amount of connections id has and store them in count).
Ordering
ORDER BY.
Views
- CREATE OR REPLACE VIEW - this would either create the virtual table or simply rewrite it with whatever is specified.
- DROP VIEW… -> drops the table
Combining Two Tables Sequentially
UNION:
Combines two tables together into one output.
select (contactFirstName + ‘ ‘ + contactLastName) as contactName FROM customers UNION
select customerName FROM customers