Databases Flashcards
How do you remove duplicates from a field in SQL?
SELECT DISTINCT(fieldname)
How to only return a subset of results from select?
LIMIT
How would you return the 10th-15th results of a select statement?
LIMIT 9,5
How do you sort by more than one column?
ORDER BY field1, field2, field3
How to do >= x or <= y?
BETWEEN x and y
How should you use quotes?
single quotes for text column names like ‘favorite food’
What do you need to escape in column names?
Single quotes need an extra quote before them
What are the logical operators?
AND, OR, IN, NOT
How to combine two fields into one?
CONCAT(thing,thing,thing)
How to rename a result?
SELECT thing AS other_thing
What arithmetic operations does it support?
+ * /
What are the LIKE metacharacers?
_ matches a single character, % matches 0 or more
How can you join without using JOIN?
Select T1.thing, T2.thing from T1,T2 WHERE T1.key = T2.key;
What are the types of JOIN? Explain each. Which is used when just JOIN is specified?
Inner join (default)- intersection of two tables, use ON keyword
Left (Outer) Join - Returns all from left table, even if there are no matches in right
Right (Outer) Join - Opposite of left join
How to combine rows from two tables in to one? How to do it by removing duplicates? What if the columns don’t’ match exactly?
select rows from T1 U selet rows from T2. U is UNION ALL, and UNION removes duplicates. Use a null for one set if it doesn’t have the field from another set.
How to add a row?
INSERT INTO table VALUES (a,b,c,d)
How to add a row with only specific fields?
INSERT INTO table (col_a, col_b, col_d) VALUES (a, b, d)
How to modify a row?
UPDATE table SET col_a=value,col_b=val WHERE condition; Without WHERE all rows get updated.
How to create a table?
CREATE TABLE name (col_name data_type(size), col_name data_type(size));
How to make a Primary Key?
when setting up the field add NOT NULL and AUTOINCREMENT after the data_type, and after a coma put PRIMARY KEY(fieldname)
What are the constraints you can put on fields?
NOT NULL, UNIQUE, CHECK (uses a logical expression to validate), DEFAULT, PRIMARY KEY
How to add a column to a table?
ALTER/MODIFY TABLE name
ADD col_name data_type
How to remove a column from a table?
ALTER/MODIFY TABLE name
drop colname;
How to delete a table?
Drop tablename;
How would you rename a column?
ALTER TABLE name CHANGE oldname newname datatype(size);
How would you rename a table?
RENAME TABLE old TO new;
What’s a view? How do you create one? How do you use it later?
A virtual table, basically a saved selection.
CREATE VIEW name AS select_statement_here
Then can use as SELECT * FROM view;
How would you update an existing view?
CREATE OR REPLACE VIEW name AS new_select_statement;
How would you delete a view?
DROP VIEW name;
