Podcast Beta
Questions and Answers
What is the primary focus of the conceptual schema?
The external schema impacts database storage.
False
What do you create for a weak entity in a database?
A table that includes the primary key of the strong entity as a foreign key and a composite primary key.
In a 1:M relationship, the primary key of the 'one' side is added as a foreign key in the ______ side.
Signup and view all the answers
Match the following relationships with their corresponding description:
Signup and view all the answers
What is the appropriate SQL command to increase Bob's salary by $5000?
Signup and view all the answers
A FULL OUTER JOIN returns only the matched records from both tables.
Signup and view all the answers
What is the primary key in the employees table?
Signup and view all the answers
The SQL command to delete an employee with the name 'Diana' is _____ employees WHERE name = 'Diana'.
Signup and view all the answers
Match the SQL command to its purpose:
Signup and view all the answers
Study Notes
Database Creation and Usage
-
CREATE DATABASE company_db;creates a new database named "company_db" -
USE company_db;selects the "company_db" database for use
Relational Database: Employees and Departments
-
CREATE TABLE employees ...sets up theemployeestable with columns:id,name,position,salary,department_id -
CREATE TABLE departments ...sets up thedepartmentstable with columns:id,department_name -
PRIMARY KEY (id)defines the primary key for each table -
department_idin theemployeestable is a foreign key referencing thedepartmentstable, establishing a relationship
Populate Tables with Data
-
INSERT INTO departments ...adds records to thedepartmentstable -
INSERT INTO employees ...adds records to theemployeestable, including department IDs to connect employees
Retrieve Data: SELECT Statement
-
SELECT * FROM employees;retrieves all data from theemployeestable -
SELECT name, position, salary FROM employees WHERE salary > 55000;retrieves data from theemployeestable with a salary greater than 55000.
Update Data: UPDATE Statement
-
UPDATE employees SET salary = salary + 5000 WHERE name = 'Bob';updates the salary of employee "Bob" by adding 5000.
Delete Data: DELETE Statement
-
DELETE FROM employees WHERE name = 'Diana';deletes the record of employee "Diana".
Aggregate Functions and Grouping
-
SELECT department_id, COUNT(*) AS employee_count FROM employees GROUP BY department_id;groups employees by department and counts the number of employees in each
String Matching: LIKE Operator
-
SELECT name FROM employees WHERE name LIKE 'A%';retrieves employee names starting with 'A'
Joins: Combining Data from Multiple Tables
-
INNER JOIN: retrieves records matchingdepartment_idbetweenemployeesanddepartmentstables -
LEFT JOIN: retrieves all records fromemployees, including any without matching departments -
RIGHT JOIN: retrieves all records fromdepartments, including any without matching employees -
FULL OUTER JOIN: retrieves all records from both tables, combining matching and non-matching entries -
CROSS JOIN: retrieves a Cartesian product of all combinations of employees and departments -
SEMI JOIN: retrieves employees with a matching department -
ANTI JOIN: retrieves employees without a matching department
Order Results: ORDER BY Clause
-
SELECT name, salary FROM employees ORDER BY salary DESC;sorts employee records by salary in descending order
Pagination: LIMIT and OFFSET
-
SELECT name, position, salary FROM employees ORDER BY salary DESC LIMIT 2 OFFSET 1;retrieves the second employee in the sorted result
Generalized SQL Pattern for Division
- The pattern is used to implement the division operator in SQL
- SELECT A.attribute FROM A JOIN Relation ON A.attribute = Relation.attribute GROUP BY A.attribute HAVING COUNT(DISTINCT Relation.attribute) = (SELECT COUNT(*) FROM B)
- Where:
-
Ais the entity you are dividing -
A.attributeis the attribute of the entity you are dividing -
Relationis the relation used for division -
Bis the specific relation involvingA
-
- This general pattern works for various types of divisions in SQL
Studying That Suits You
Use AI to generate personalized quizzes and flashcards to suit your learning preferences.
Related Documents
Description
Test your knowledge on creating and using relational databases with a focus on setting up tables and relationships. This quiz covers data definitions, database manipulation, and data retrieval using SQL statements. Perfect for anyone studying database management systems.
