12 Questions
Which SQL statement is used to define the structure of a database?
CREATE TABLE Students ( StudentID INT PRIMARY KEY, Name VARCHAR(50), Age INT, GPA FLOAT);
What is the result of an arithmetic expression involving a NULL value in SQL?
The result of the expression is NULL
What does the predicate 'is null' do in SQL?
Checks if a field has a NULL value
Which clause is used in SQL to filter rows based on specified conditions?
WHERE
When creating a database, what does the 'IF NOT EXISTS' clause do?
Creates a new database if it doesn't already exist
Which SQL clause is used to rename a column or table with an alias?
AS
In SQL, how are primary keys typically mentioned in a table definition statement?
OrderID INT PRIMARY KEY
What does the GROUP BY clause do in SQL?
Groups rows based on specified columns for aggregate functions
When is the HAVING clause used in SQL?
To filter rows based on aggregate conditions
What does the IS NULL operator do in SQL?
Filters rows where a column's value is NULL
Which SQL operation is used to search for a specified pattern in a column using the % wildcard?
LIKE and %
Why are aggregate functions useful when writing queries with GROUP BY in SQL?
To perform calculations on groups of rows
Study Notes
Database Management
- DDL (Data Definition Language) is concerned with defining and managing the structure of the database
- DML (Data Manipulation Language) is concerned with manipulating the data stored within the database
NULL Values
- NULL values are different from zero values
- The result of any arithmetic expression involving NULL is NULL (e.g., 5+NULL returns NULL)
- The predicate "IS NULL" can be used to check for NULL values
- A field with a NULL value is one that has been left blank during record creation
- Unmatched values in a join show NULL values, especially important when working with outer joins
Creating a Database and Tables
- CREATE DATABASE: CREATE DATABASE YourDatabaseName;
- CREATE SCHEMA: CREATE SCHEMA YourSchemaName;
- CREATE TABLE: CREATE TABLE Students (StudentID INT PRIMARY KEY, Name VARCHAR(50), Age INT, GPA FLOAT);
- IF EXISTS: WHERE EXISTS
- IF NOT EXISTS: CREATE DATABASE IF NOT EXISTS YourDatabaseName;
SQL Types
- INT: whole numbers
- VARCHAR(50): variable-length character string up to 50 characters
- FLOAT: decimal number
- BOOLEAN: true or false values
- DECIMAL(8,2): decimal number with 8 digits, 2 of which are after the decimal point
Primary Keys
- PRIMARY KEY: specifies the primary key of a table
- Example: OrderID INT PRIMARY KEY
Basic SQL Clauses
- SELECT: Retrieves data from one or more tables
- WHERE: Filters rows based on specified conditions
- FROM: Specifies the table(s) from which to retrieve data
- AS: Renames a column or table with an alias
- ORDER BY: Sorts the result set based on specified columns
- AVG, MIN, MAX, SUM, COUNT: Aggregate functions for calculations on numeric columns
- GROUP BY: Groups rows based on specified columns for aggregate functions
- HAVING: Filters groups based on aggregate conditions
- IS NULL and IS NOT NULL: Filter rows based on NULL values
- DISTINCT: Retrieves unique values from a column
- Cartesian Product: Combines every row from one table with every row from another table
Query Operations
- Predicates in the WHERE clause with arithmetic expressions and Boolean operators
- String Operations: LIKE and % wildcard
- UNION: Combines the result sets of two or more SELECT statements
- Nested subqueries in the WHERE clause for determining set membership or comparing two sets
Learn about the distinction between Data Definition Language (DDL) and Data Manipulation Language (DML) in databases. Understand how DDL is used for managing the structure of the database, while DML is focused on manipulating stored data. Explore the behavior of null values in queries and their impact on arithmetic operations and predicate checks.
Make Your Own Quizzes and Flashcards
Convert your notes into interactive study material.
Get started for free
