Schemas
Name the two requirements of a primary key.
UNIQUE and NOT NULL
Create a cursor out of the connection object.
cursor = connection.cursor()
Write a query that returns the course_id's of all 100 level courses.
SELECT course_id FROM course WHERE course_id < 200;
Write a query that returns the names of all students that start with a "C".
SELECT name FROM student WHERE name like "C%";
UPDATE the student table to change GPAs of students with names that start with "B" to 4.0
UPDATE student SET gpa = 4.0 WHERE name LIKE "B%";
CREATE TABLE Student (
name VARCHAR(255),
birthday DATETIME,
age INT
);
Give a valid primary key for this schema.
PRIMARY KEY (name, birthday)?
OR
PRIMARY KEY (name, birthday, age)?
Assume a proper query has been run using cursor.execute()
If output = cursor.fetchall(),
what is the data type of output, how would you access the first value, and what is the data type of the first value?
A list, output[0], a dictionary.
Write a query that returns the names of all student enrolled in courses in the CS department.
SELECT DISTINCT name FROM student JOIN enrolled USING (student_id) WHERE dept_id = "CS";
Write a query that returns the name(s) of the dean(s) and their department(s) whose department has a course that has an id of 101.
SELECT dean, dept.name FROM course JOIN dept USING (dept_id) WHERE course_id = 101;
Update the department table to change the dean and building of any CS departments to "Burdell", "IC" respectively.
UPDATE dept SET dean = "Burdell", building = "IC" WHERE dept_id = "CS";
What three lines do you need at the start of every schema you create and what purpose do they serve?
DROP DATABASE IF EXISTS dbname;
CREATE DATABASE dbname;
USE DATABASE dbname;
Using the cursor object, change the GPA of a student named Bush to 4.00 in the "student" table.
cursor.execute("UPDATE student SET gpa = 4.00 WHERE name = "Bush")
Write a query that returns the names and GPAs of all students enrolled in 4-hour courses.
SELECT DISTINCT student.name, student.gpa FROM student JOIN enrolled USING (student_id) JOIN course USING (course_id) WHERE hours = 4;
OR
SELECT DISTINCT name, gpa FROM student JOIN enrolled USING (student_id) WHERE course_id IN (SELECT course_id FROM course WHERE hours = 4);
Write a query that returns the dept_ids and names of the deans of departments that have zero enrollment
SELECT dept_it, dean FROM dept LEFT JOIN enrolled USING (dept_id) WHERE course_id IS NULL;
Delete any records in enrollment where a student has a 3.0 GPA or less.
DELETE FROM enrolled WHERE student_id IN (SELECT student_id FROM student WHERE gpa <= 3.0);
CREATE TABLE Department (
name VARCHAR(255),
ID INT,
PRIMARY KEY (ID)
);
CREATE TABLE Course (
name VARCHAR(255),
course_ID INT,
dept VARCHAR(255),
PRIMARY KEY (name, course)
FOREIGN KEY (dept) REFERENCES (Department.ID)
)
Point out the two errors.
A missing semicolon at the end of the Course table.
Mismatching types with Course table's foreign key and Department table's primary key.
Using the cursor object, select all the rows from the "student" table and store returned rows from the query in a variable called output.
cursor.execute("Select * from student;")
output = cursor.fetchall()
Write a query that returns the buildings that student "Bush" has classes in.
SELECT building FROM dept JOIN enrolled USING (student_id_ WHERE student_id IN (SELECT student_id FROM student WHERE name = "Bush");
Write a query that returns the dept_ids and names of the deans of departments that have zero enrollment
SELECT dept_it, dean FROM dept LEFT JOIN enrolled USING (dept_id) WHERE course_id IS NULL;
Insert a new student into the student table. Create your own values.
INSERT INTO student VALUES (24, "Suzie", 2.4);
CREATE TABLE Course (
name VARCHAR(255),
course_ID INT,
dept VARCHAR(255),
PRIMARY KEY (name, course)
);
Write a Teacher table with attributes: name, ID, and a foreign key for the course the teacher teaches.
CREATE TABLE Teacher (
name VARCHAR(255),
ID INT,
course_name VARCHAR(255),
course_ID INT,
PRIMARY KEY (ID),
FOREIGN KEY (course_name, course_ID) REFERENCES (Course.name, Course.course_ID)
);
Assume we are using the registration database. Fill in the blanks/explain what should go in each blank...
import pymysql
connection = pymysql.connect(host = ____, username = ____, password = "csrox", db = ____, charset = ____, cursorclass = ____)
"localhost", "root", "registration", "utf8mb4", pymysql.cursors.DictCursor ?
Calculate the total number of credit hours of all the courses for each department that have more than one course.
SELECT dept_id, SUM(hours) FROM course GROUP BY dept_id HAVING COUNT(course_id) > 1;
Write a query that returns the number of students enrolled in each department. Include department id and enrollment in result.
SELECT dept_id, COUNT(student_id) FROM dept LEFT JOIN enrolled USING (dept_id) GROUP BY dept_id;
You have selected the special holiday question. The TAs must now play "All I Want for Christmas Is You" to the entire class.