Database Creation and Usage Quiz

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 the employees table with columns: id, name, position, salary, department_id
• CREATE TABLE departments ... sets up the departments table with columns: id, department_name
• PRIMARY KEY (id) defines the primary key for each table
• department_id in the employees table is a foreign key referencing the departments table, establishing a relationship

Populate Tables with Data

• INSERT INTO departments ... adds records to the departments table
• INSERT INTO employees ... adds records to the employees table, including department IDs to connect employees

Retrieve Data: SELECT Statement

• SELECT * FROM employees; retrieves all data from the employees table
• SELECT name, position, salary FROM employees WHERE salary > 55000; retrieves data from the employees table 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 matching department_id between employees and departments tables
• LEFT JOIN: retrieves all records from employees, including any without matching departments
• RIGHT JOIN: retrieves all records from departments, 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:
  • A is the entity you are dividing
  • A.attribute is the attribute of the entity you are dividing
  • Relation is the relation used for division
  • B is the specific relation involving A
• This general pattern works for various types of divisions in SQL

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.