Lesson 1 Flashcards
What are the main parts of SQL?
DML (Data Manipulation Language) – Query and modify data (SELECT, INSERT, UPDATE, DELETE).
DDL (Data Definition Language) – Define database structure (CREATE, ALTER, DROP).
DCL (Data Control Language) – Manage access (GRANT, REVOKE).
TCL (Transaction Control Language) – Manage transactions (COMMIT, ROLLBACK).n
What command is used to create a database in PostgreSQL
CREATE DATABASE datam_test;
What is the SQL command to create a table?
CREATE TABLE classroom (
building VARCHAR(15) NOT NULL,
room_number VARCHAR(7) NOT NULL,
capacity NUMERIC(4,0) NOT NULL,
PRIMARY KEY (building, room_number)
);
What are integrity constraints?
Rules ensuring data consistency:
Primary Key – Uniquely identifies a record.
Foreign Key – Ensures referential integrity.
Not Null – Ensures a column cannot be empty.
Unique – Ensures all values are distinct.
Check – Ensures values meet a condition.
How do you insert data into a table?
INSERT INTO instructor (ID, name, dept_name, salary)
VALUES (‘102’, ‘Alice’, ‘Math’, 85000);
How do you select all records from a table?
SELECT * FROM instructor;
How do you retrieve unique values?
SELECT DISTINCT dept_name FROM instructor;
How do you filter records?
SELECT name FROM instructor WHERE dept_name = ‘Comp. Sci.’;
How do you sort query results?
SELECT name FROM instructor ORDER BY name DESC;
What is the SQL pattern matching operator?
LIKE
How do you find names containing “ri”?
SELECT name FROM instructor WHERE name LIKE ‘%ri%’;
What does % and _ do in LIKE queries
% – Matches any sequence of characters.
_ – Matches a single character.
What are SQL set operations?
UNION – Combines results (removes duplicates).
INTERSECT – Finds common elements.
EXCEPT – Finds elements in one set but not the other.
Example of UNION operation?
SELECT course_id FROM section WHERE semester = ‘Fall’ AND year = 2017
UNION
SELECT course_id FROM section WHERE semester = ‘Spring’ AND year = 2018;
What are the common aggregate functions in SQL?
AVG() – Calculates average.
SUM() – Calculates sum.
COUNT() – Counts rows.
MAX() – Finds maximum value.
MIN() – Finds minimum value.
How do you find the average salary in the CS department?
SELECT AVG(salary) FROM instructor WHERE dept_name = ‘Comp. Sci.’;
What does GROUP BY do?
Groups rows based on column values for aggregation.
What is HAVING used for?
Filters groups after aggregation.
Example of grouping by department?
SELECT dept_name, AVG(salary) FROM instructor GROUP BY dept_name;
Example of filtering departments with avg salary > 42000?
SELECT dept_name, AVG(salary) FROM instructor GROUP BY dept_name HAVING AVG(salary) > 42000;
What is a subquery?
A query inside another query.
Example of a subquery to find instructors earning more than all Biology instructors?
SELECT name FROM instructor WHERE salary > ALL
(SELECT salary FROM instructor WHERE dept_name = ‘Biology’);
How do you delete all instructors from the Finance department?
DELETE FROM instructor WHERE dept_name = ‘Finance’;
How do you update salaries by 5%?
UPDATE instructor SET salary = salary * 1.05;
How do you increase salaries for instructors earning less than 70000?
UPDATE instructor SET salary = salary * 1.05 WHERE salary < 70000;
What is the WITH clause used for?
Defines a temporary relation used in the main query.
Example of using WITH to find departments with max budget?
WITH max_budget (value) AS (SELECT MAX(budget) FROM department)
SELECT dept_name FROM department, max_budget WHERE department.budget = max_budget.value;
