Databases

Question 1

How do you remove duplicates from a field in SQL?

Answer 1

SELECT DISTINCT(fieldname)

Question 2

How to only return a subset of results from select?

Answer 2

LIMIT

Question 3

How would you return the 10th-15th results of a select statement?

Answer 3

LIMIT 9,5

Question 4

How do you sort by more than one column?

Answer 4

ORDER BY field1, field2, field3

Question 5

How to do >= x or <= y?

Answer 5

BETWEEN x and y

Question 6

How should you use quotes?

Answer 6

single quotes for text column names like ‘favorite food’

Question 7

What do you need to escape in column names?

Answer 7

Single quotes need an extra quote before them

Question 8

What are the logical operators?

Answer 8

AND, OR, IN, NOT

Question 9

How to combine two fields into one?

Answer 9

CONCAT(thing,thing,thing)

Question 10

How to rename a result?

Answer 10

SELECT thing AS other_thing

Question 11

What arithmetic operations does it support?

Answer 11

+ * /

Question 12

What are the LIKE metacharacers?

Answer 12

_ matches a single character, % matches 0 or more

Question 13

How can you join without using JOIN?

Answer 13

Select T1.thing, T2.thing from T1,T2 WHERE T1.key = T2.key;

Question 14

What are the types of JOIN? Explain each. Which is used when just JOIN is specified?

Answer 14

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

Question 15

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?

Answer 15

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.

Question 16

How to add a row?

Answer 16

INSERT INTO table VALUES (a,b,c,d)

Question 17

How to add a row with only specific fields?

Answer 17

INSERT INTO table (col_a, col_b, col_d) VALUES (a, b, d)

Question 18

How to modify a row?

Answer 18

UPDATE table SET col_a=value,col_b=val WHERE condition; Without WHERE all rows get updated.

Question 19

How to create a table?

Answer 19

CREATE TABLE name (col_name data_type(size), col_name data_type(size));

Question 20

How to make a Primary Key?

Answer 20

when setting up the field add NOT NULL and AUTOINCREMENT after the data_type, and after a coma put PRIMARY KEY(fieldname)

Question 21

What are the constraints you can put on fields?

Answer 21

NOT NULL, UNIQUE, CHECK (uses a logical expression to validate), DEFAULT, PRIMARY KEY

Question 22

How to add a column to a table?

Answer 22

ALTER/MODIFY TABLE name

ADD col_name data_type

Question 23

How to remove a column from a table?

Answer 23

ALTER/MODIFY TABLE name

drop colname;

Question 24

How to delete a table?

Answer 24

Drop tablename;

Question 25

How would you rename a column?

Answer 25

ALTER TABLE name CHANGE oldname newname datatype(size);

Question 26

How would you rename a table?

Answer 26

RENAME TABLE old TO new;

Question 27

What’s a view? How do you create one? How do you use it later?

Answer 27

A virtual table, basically a saved selection.

CREATE VIEW name AS select_statement_here

Then can use as SELECT * FROM view;

Question 28

How would you update an existing view?

Answer 28

CREATE OR REPLACE VIEW name AS new_select_statement;

Question 29

How would you delete a view?

Answer 29

DROP VIEW name;