Unit III - SQL & MySQL Notes

Podcast

Play an AI-generated podcast conversation about this lesson

Questions and Answers

What does the COUNT(*) function return when used in SQL?

All rows, including duplicates and nulls.

What is the output of the query SELECT COUNT(JOB) FROM EMPL?

What is the purpose of the MAX() function in SQL?

It returns the maximum value from a given column or expression.

What does the MIN() function in SQL return?

The minimum value from a given column or expression. Signup and view all the answers

What does the SUM() function do in SQL?

It returns the sum of values in a given column or expression. Signup and view all the answers

What is the purpose of the GROUP BY clause in SQL?

It combines records that have identical values in a particular field or group of fields. Signup and view all the answers

What does the HAVING clause do in the context of SQL queries?

It places conditions on groups created by the GROUP BY clause. Signup and view all the answers

What is the definition of a transaction in database terms?

A logical unit of work that must succeed or fail in its entirety. Signup and view all the answers

Which of the following are ACID properties of transactions? (Select all that apply)

Atomicity Signup and view all the answers

What is the function of the COMMIT command in MySQL?

It ends the current transaction by saving database changes. Signup and view all the answers

What does the ROLLBACK command do?

It ends the current transaction by discarding database changes. Signup and view all the answers

What is MySQL?

A freely available open source Relational Database Management System (RDBMS) that uses Structured Query Language (SQL). Signup and view all the answers

Which of the following is not a data model used for database management?

Document data model Signup and view all the answers

A row of a relation is generally referred to as a ______.

tuple Signup and view all the answers

What does the term 'cardinality' refer to in a relational model?

The number of tuples in a relation. Signup and view all the answers

A foreign key is a non-key attribute with values derived from the primary key of some other table.

True Signup and view all the answers

What command is used to create a database in MySQL?

CREATE DATABASE <database_name>; Signup and view all the answers

What keyword is used in SQL to retrieve all columns from a table?

SELECT * Signup and view all the answers

The SQL command for deleting a database is ______.

DROP DATABASE Signup and view all the answers

Which SQL command is used to insert data into a table?

INSERT INTO <table_name> VALUES (...); Signup and view all the answers

The DISTINCT keyword in SQL is used to select all rows including duplicates.

False Signup and view all the answers

What operator is used for pattern matching in SQL?

LIKE Signup and view all the answers

What command is used to select employee names with a gross salary greater than 40000?

SELECT ENAME FROM EMPLOYEE WHERE GROSS > 40000 ORDER BY ENAME DESC; Signup and view all the answers

What is the syntax for the UPDATE command in SQL?

UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition; Signup and view all the answers

How do you change the salary of an employee with employee code 1009 to 55000?

UPDATE EMPLOYEE SET GROSS = 55000 WHERE ECODE = 1009; Signup and view all the answers

The DELETE command can remove an entire table and its structure.

False Signup and view all the answers

What command is used to delete all contents from a table?

DELETE FROM EMPLOYEE; Signup and view all the answers

Write the command to drop the 'employee' table from a database.

DROP TABLE employee; Signup and view all the answers

The command to add a new column to a table is __________.

ALTER TABLE Signup and view all the answers

What is the default behavior of a NOT NULL constraint in SQL?

It ensures that a column cannot have NULL values. Signup and view all the answers

Which of the following describes the UNIQUE constraint?

Ensures all values in a column are different. Signup and view all the answers

Match the SQL command with its function:

SELECT = Retrieve data from a database INSERT = Add new data to a database UPDATE = Modify existing data in a database DELETE = Remove data from a database Signup and view all the answers

What happens when you try to insert a value that violates the UNIQUE constraint?

An error occurs. Signup and view all the answers

Study Notes

MySQL Overview

MySQL is a free, open-source Relational Database Management System (RDBMS) that utilizes Structured Query Language (SQL).
Information in MySQL is organized in tables, allowing for multiple tables with thousands of records in a single database.

Structured Query Language (SQL)

SQL enables the creation and management of relational databases, where related data is stored in tables.

Data Models

A data model describes a database's structure and constraints.
Major data models include:
- Relational Data Model: Organizes data into tables (relations).
- Hierarchical Data Model
- Network Data Model
- Object-Oriented Data Model

Relational Model Terminology

Relation: A table of logically related data.
Tuple: A row within a relation.
Attribute: A column within a relation.
Degree: The number of attributes in a relation.
Cardinality: The number of tuples in a relation.
Primary Key: A unique identifier for tuples in a relation.
Candidate Key: Potential primary keys formed by attribute combinations.
Alternate Key: A candidate key that is not the primary key.
Foreign Key: A non-key attribute that references a primary key from another table.

Referential Integrity

A system of rules that maintains valid relationships between records in related tables, preventing accidental data loss or alteration.

Classification of SQL Statements

Data Definition Language (DDL): Commands for defining data structures, e.g., creating and modifying tables.
Data Manipulation Language (DML): Commands for manipulating data, e.g., inserting, updating, deleting records.
Transaction Control Language (TCL): Commands for managing transactions, e.g., committing or rolling back changes.

MySQL Elements

Literals: Fixed data values that can be character or numeric types.
Data Types: Identify data types and operations, divided into Numeric, Date and Time, and String types.
- Numeric:
  - int: For integers.
  - Decimal(m,d): For floating-point numbers.
- Date and Time:
  - date: Format YYYY-MM-DD.
  - time: Format HH:MM:SS.
- String:
  - char(m): Fixed-length strings.
  - varchar(m): Variable-length strings.

NULL Values

A column is marked as NULL when it contains no value, indicating an unknown or irrelevant value.

Basic Database Commands

Show Existing Databases: SHOW DATABASES;
Create Database: CREATE DATABASE database_name;
Use Database: USE database_name;
Drop Database: DROP DATABASE database_name;
Show Tables: SHOW TABLES;

Creating Tables

Use the CREATE TABLE command to define a table’s structure.
Example syntax:
CREATE TABLE table_name (column1 data_type, column2 data_type, ...);

Inserting Data

Use the INSERT command to add rows to tables.
Syntax: INSERT INTO table_name VALUES (value1, value2, ...);
For NULL values, specify NULL without quotes.

Selecting Data

All Data: SELECT * FROM table_name;
Specific Columns: SELECT column1, column2 FROM table_name;
Conditional Selection: Use WHERE clause, e.g., SELECT * FROM table_name WHERE condition;
Distinct Values: Use the DISTINCT keyword to eliminate duplicate entries, e.g., SELECT DISTINCT(column) FROM table_name;.

Viewing Table Structure

Use the command DESCRIBE table_name; to view schema details.

Column Aliases

Assign aliases using AS to rename columns in output, e.g., SELECT column AS alias_name FROM table_name;

Condition-Based Queries

Use BETWEEN to specify a range, and IN for a list of possible values.
Utilize the LIKE operator for pattern matching, leveraging % for any substring and _ for a single character.

Searching for NULL Values

Use IS NULL to find NULL entries in a table.

Sorting Results

The ORDER BY clause sorts query results in ascending (default) or descending order.

Modifying Data

Use the UPDATE command to change existing records, specifying conditions with WHERE.
Example: UPDATE table_name SET column1 = value WHERE condition;

Deleting Data

Use the DELETE command to remove records from tables, with the option to delete all rows by omitting the WHERE clause.### Dropping Tables
The DROP TABLE command removes a table from the database permanently.
Syntax: DROP TABLE <table_name>;
Example: DROP TABLE employee; deletes the employee table and all its data.
Once dropped, the table name cannot be referenced, and all data and structure are lost.
Differentiates from the DELETE command, which only removes rows without affecting the table structure.

Alter Table Command

The ALTER TABLE command modifies existing table definitions such as adding, deleting, or modifying columns.
Adding a column example:
ALTER TABLE <table_name> ADD <column_name> <data_type>;
- Example: ALTER TABLE EMPLOYEE ADD ADDRESS VARCHAR(50);
Default values are assigned when NOT NULL constraints are applied during column addition.

Modifying Columns

Syntax to change column name and definition:
ALTER TABLE <table_name> CHANGE <old_column_name> <new_column_name> <data_type>;
To only change data type, use:
ALTER TABLE <table_name> MODIFY <column_name> <new_data_type>;
Examples include changing GROSS to SALARY and modifying ENAME to EM_NAME.

Deleting Columns

Syntax to drop a column:
ALTER TABLE <table_name> DROP <column_name>;
Example: ALTER TABLE EMPLOYEE DROP GRADE;

Adding/Removing Constraints

To add a PRIMARY KEY:
ALTER TABLE <table_name> ADD PRIMARY KEY (<column_name>);
To add a FOREIGN KEY:
ALTER TABLE <table_name> ADD FOREIGN KEY (<column_name>) REFERENCES <parent_table>(<parent_column>);
Removal of constraints uses:
ALTER TABLE <table_name> DROP PRIMARY KEY; and
ALTER TABLE <table_name> DROP FOREIGN KEY;

Enabling/Disabling Constraints

Foreign keys can be enabled/disabled using:
- Disable: SET FOREIGN_KEY_CHECKS = 0;
- Enable: SET FOREIGN_KEY_CHECKS = 1;

Integrity Constraints

Constraints ensure data integrity. Types include:
- NOT NULL: Prevents NULL values.
- DEFAULT: Sets a default value for a column.
- UNIQUE: Ensures all values are distinct.
- CHECK: Ensures specific criteria are met.
- PRIMARY KEY: Uniquely identifies a row.
- FOREIGN KEY: Enforces referential integrity between tables.

Aggregate / Group Functions

Aggregate functions operate on groups of rows and return a single result.
Common functions:
- COUNT(): Counts rows.
- AVG(): Averages values.
- SUM(): Sums values.
- MIN(): Finds minimum value.
- MAX(): Finds maximum value.
Example usage:
- SELECT AVG(SAL) FROM EMPL; returns the average salary.
- SELECT COUNT(*) FROM EMPL; counts total rows.

Grouping Results – GROUP BY

GROUP BY combines records with identical values in specified columns.
Example:
- SELECT JOB, COUNT(*) FROM EMPL GROUP BY JOB; counts employees per job.
Multiple fields can be grouped leading to nested aggregation.

Placing Conditions on Groups – HAVING Clause

HAVING places conditions on group results, unlike WHERE, which filters individual rows.
Example:
- SELECT JOB, COUNT(*) FROM EMPL GROUP BY JOB HAVING COUNT(*) < 2; retrieves jobs with fewer than two employees.

Database Transactions

A transaction is a set of operations performed as a single logical unit, ensuring all steps succeed or the entire transaction fails.### Transactions
A transaction is an atomic operation, indivisible into smaller operations.
Example sequence:
- Start transaction
- Retrieve balance from account X, calculate new balance (X - 1000), save to database.
- Retrieve balance from account Y, calculate new balance (Y + 1000), save to database.
- End transaction.

ACID Properties

Atomicity: Ensures either all operations are completed successfully or none are executed. This maintains data integrity.
Consistency: Guarantees that if the database is in a consistent state before a transaction, it remains consistent afterward.
Isolation: Each transaction operates independently from others, preventing interference and ensuring data integrity during concurrent execution.
Durability: After a successful transaction, changes persist in the database even in case of system failures.

Transaction Control Commands (TCL) in MySQL

BEGIN/START TRANSACTION: Initiates a new transaction.
COMMIT: Completes the current transaction, saving changes and starting a new one.
ROLLBACK: Cancels the current transaction, discarding changes and starting a new transaction.
SAVEPOINT: Allows the definition of breakpoints within a transaction for partial rollbacks.
SET AUTOCOMMIT: Enables or disables the automatic commit mode for transactions.

Studying That Suits You

Use AI to generate personalized quizzes and flashcards to suit your learning preferences.

Description

Explore the fundamentals of SQL and MySQL in this quiz. Learn about how MySQL serves as a relational database management system using Structured Query Language. This unit delves into the structure, tables, and data models involved in effectively utilizing MySQL.