SQL Flashcards

1
Q

Why are SQL commands written in all caps?

A

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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

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

A

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)
);

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

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

A

DESCRIBE student;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

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

A

DROP TABLE student;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

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

A

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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

Do all commands need a semicolon after them?

A

Yes, always use a semicolon after a command.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

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?

A

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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

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

A

ALTER TABLE student DROP COLUMN gpa;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

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

A

No, both will succeed.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

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

A

SELECT * FROM student;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

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

A

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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

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

A

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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

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

A

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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

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

A

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

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

Do line breaks affect SQL commands?

A

No, the semicolon indicated the end of a statement.

17
Q

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

A

UPDATE student
SET major = “undecided”;

18
Q

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?

A

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

19
Q

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)?

A

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;

20
Q

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

A

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

21
Q

How do you comment out a line?

A


two dashes

22
Q

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?

A

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

23
Q

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?

A

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.

24
Q

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

A

SELECT first_name AS forename, last_name AS surname
FROM employee;

25
Q

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?

A

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.

26
Q

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

A

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.

27
Q

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

A

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