SQL Flashcards
Why are SQL commands written in all caps?
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.
Describe a SQL query that creates a table in a database.
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)
);
What command would you use to show information about table “student”?
DESCRIBE student;
What command would you use to delete table “student”?
DROP TABLE student;
What command would you use to add a GPA element to table “student”?
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.
Do all commands need a semicolon after them?
Yes, always use a semicolon after a command.
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?
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.
What command would you use to drop the table element gpa in SQL table “student”?
ALTER TABLE student DROP COLUMN gpa;
What command would you use to insert a student into table “student”? Students have a student_id, name, and major.
INSERT INTO student VALUE(1, ‘Thomas’, ‘CS’);
Does it matter if you use quotes (“) or single quotes (‘) to wrap strings?
No, both will succeed.
What command would you use to get everything from table “student”?
SELECT * FROM student;
What command would you use to insert an entry into table “student” without including all the fields? Specific fields?
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.
What are the different constraints you may specify when creating a SQL table?
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.
What command would you use to change students majors to “bio” that are currently called “biology” in table “student”? Or chemistry?
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.
What command would you use to change student with student_id 1 to name “Brad” and major “undecided”?
UPDATE student
SET name = “Brad”, major = “undecided”
WHERE student_id = 1;
You can include or exclude line endings. Statement only ended by semicolon.