SQL

Question 1

Why are SQL commands written in all caps?

Answer 1

It’s a convention to distinguish the actual SQL commands from the other text. It could be done in lower case, but that is not conventional.

Question 2

Describe a SQL query that creates a table in a database.

Answer 2

CREATE TABLE student (
student_id INT PRIMARY KEY,
name VARCHAR(30),
major VARCHAR(20)
);

or

CREATE TABLE student (
student_id INT,
name VARCHAR(30),
major VARCHAR(20),
PRIMARY KEY(student_id)
);

Question 3

What command would you use to show information about table “student”?

Answer 3

DESCRIBE student;

Question 4

What command would you use to delete table “student”?

Answer 4

DROP TABLE student;

Question 5

What command would you use to add a GPA element to table “student”?

Answer 5

ALTER TABLE student ADD gpa DECIMAL(3, 2);

DECIMAL(3, 2) means there are 3 total digits, 2 of them are after the decimal point.

Question 6

Do all commands need a semicolon after them?

Answer 6

Yes, always use a semicolon after a command.

Question 7

Are table elements represented in the columns or the rows of a SQL database? (If we have a gpa field for a student, is that a row or a column?) Is it the same when using DESCRIBE?

Answer 7

Columns. So, when deleting an element like GPA, you would use DROP COLUMN gpa. When using DESCRIBE, these fields should as rows. The properties of these elements are shown in the columns, like if they can be null, if they’re part of a key, or if they have a default value.

Question 8

What command would you use to drop the table element gpa in SQL table “student”?

Answer 8

ALTER TABLE student DROP COLUMN gpa;

Question 9

What command would you use to insert a student into table “student”? Students have a student_id, name, and major.

Answer 9

INSERT INTO student VALUE(1, ‘Thomas’, ‘CS’);

Question 10

Does it matter if you use quotes (“) or single quotes (‘) to wrap strings?

Answer 10

No, both will succeed.

Question 11

What command would you use to get everything from table “student”?

Answer 11

SELECT * FROM student;

Question 12

What command would you use to insert an entry into table “student” without including all the fields? Specific fields?

Answer 12

INSERT INTO student(student_id, name) VALUE(1, “Thomas”);
or
INSERT INTO student VALUE(1, “Thomas”, NULL);

The first option sets all other values not specified in the parentheses (after student) to null. The values in VALUE correspond to the first parentheses values.

Question 13

What are the different constraints you may specify when creating a SQL table?

Answer 13

CREATE TABLE student (
student_id INT AUTO_INCREMENT,
name VARCHAR(30) NOT NULL,
major VARCHAR(20) UNIQUE,
fav_candy VARCHAR(15) DEFAULT ‘undecided’,
PRIMARY KEY(student_id)
);

AUTO_INCREMENT, NOT NULL, UNIQUE, DEFAULT ‘value’.
Primary key just means unique and not null. Also used to sort I believe.

Question 14

What command would you use to change students majors to “bio” that are currently called “biology” in table “student”? Or chemistry?

Answer 14

UPDATE student
SET major = “bio”
WHERE major = “biology”;

UPDATE student
SET major = “bio”
WHERE major = “biology” OR major = “chemistry”;

You can include or exclude line endings. Statement only ended by semicolon.

Question 15

What command would you use to change student with student_id 1 to name “Brad” and major “undecided”?

Answer 15

UPDATE student
SET name = “Brad”, major = “undecided”
WHERE student_id = 1;

You can include or exclude line endings. Statement only ended by semicolon.

Question 16

Do line breaks affect SQL commands?

Answer 16

No, the semicolon indicated the end of a statement.

Question 17

What command would you use to set every entry in table student to major “undecided”?

Answer 17

UPDATE student
SET major = “undecided”;

Question 18

What command would you use to delete every student with name “Tom” from table “student”? Name Tom and major CS? Name Tom or name Matt? Every entry?

Answer 18

DELETE FROM student
WHERE name = “Tom”;

DELETE FROM student
WHERE name = “Tom” AND major = “CS”;

DELETE FROM student
WHERE name = “Tom” OR name = “Matt”;

DELETE FROM student; <– all entries deleted

Question 19

What command would you use to select everything from table “student”? Every entry where name is Tom? Every entry where name is Tom or Matt? Select name and major from only 2 entries in the table with name Sara or Laura, ordered by major then student_id (descending)?

Answer 19

SELECT * FROM student;

SELECT * FROM student
WHERE name = Tom;

SELECT * FROM student
WHERE name = Tom OR name = Matt;

SELECT student.name, student.major
FROM student
WHERE name = Sara OR name = Laura
ORDER BY major, student_id DESC
LIMIT 2;

Question 20

What are all the different operators possible in a WHERE statement?

Answer 20

= equal to
< less than
> greater than
<= less than or equal to
>= greater than or equal to
<> not
AND
OR

Question 21

How do you comment out a line?

Answer 21

–
two dashes

Question 22

What command would you use to select entries with name Tom, Matt, or Sara without using the OR operator and student_id greater than 2?

Answer 22

SELECT *
FROM student
WHERE name IN (‘Tom’, ‘Matt’, ‘Sara’) AND student_id > 2;

Question 23

How would you specify a foreign key when creating a table? When a table is already created? Why would you need to do it after it’s already created?

Answer 23

CREATE TABLE branch(
branch_id INT PRIMARY KEY,
branch_name VARCHAR(20),
mgr_id INT,
mgr_start_date DATE,
FOREIGN KEY(mgr_id) REFERENCES employee(emp_id) ON DELETE SET NULL
);

ALTER TABLE employee
ADD FOREIGN KEY(branch_id)
REFERENCES branch(branch_id)
ON DELETE SET NULL;

You would need to add the foreign key after the table is created if the foreign key references a field inside of itself or another table that has not been created. A foreign key must reference something that already exits, which isn’t always true when initially creating table.

Question 24

How would you select first and last names from table employee as forename and surname?

Answer 24

SELECT first_name AS forename, last_name AS surname
FROM employee;

Question 25

How do you select all the different majors stored in the element major in table “student”? Does it select each of a given major or just one for each?

Answer 25

SELECT DISTINCT major
FROM student;

Gives only one instance of an existing major to show it exists in the table. Does not give complete entry. Just the different major names.

Question 26

How would you get the number of employees from table “employee”? Number of male employees? Does this count fields with null values?

Answer 26

SELECT COUNT(emp_id)
FROM employees;

SELECT COUNT(emp_id)
FROM employees
WHERE gender = ‘M’;

Does not count fields with a NULL value. So even if an entry is in a table with a super_id value and it is NULL, using SELECT COUNT(super_id) won’t count that entry.

Question 27

How would you get the average male salary from table “employee”?

Answer 27

SELECT AVG(salary)
FROM employee
WHERE gender = ‘M’;