Chapter 3 - SQL (Structured Query Language)

Question 1

What are some Characteristics of SQL?

Answer 1

It is not a complete programming language, but rather a data sublanguage.
It was developed by IBM in the late 1970s. Endorsed and adopted by ANSI in 1992

Question 2

What are the SQL Statement Categories?

Answer 2

Data Definition Language (DDL) - Used to create DB structures
Data Manipulation Language (DML) - Used to query, insert, modify, and delete
SQL/Persistent Stored Modules (SQL/PSM) - Extends SQL by adding procedural programming capabilities
Transaction Control Language (TCL) - Marks transaction boundaries
Data Control Language (DCL) - Grants and revokes DB permissions

Question 3

How do you Describe the Structure of a Table in MySQL?

Answer 3

Use the DESC statement. Example: “DESC DEPARTMENT”

Question 4

What does the * mean when Querying?

Answer 4

• means “all”

When using the query “SELECT * FROM ____” you are selecting all applicable data from the relation

Question 5

What are the basic SQL DDL Statements for Creating Database Structures?

Answer 5

CREATE - creates DB objects
ALTER - Modify the structure and/or characteristics of DB objects
DROP - To delete DB objects
TRUNCATE - Delete table data while keeping the structure

Question 6

What is the SQL CREATE TABLE statement format?

Answer 6

CREATE TABLE NEW_TABLE_NAME(
ColumnName DataType OptionalColumnConstraints
ColumnName DataType OptionalColumnConstraints
ColumnName DataType OptionalColumnConstraints

Question 7

What are the Different Data Types available in all DBMS products? (Pg. 146)

Answer 7

Numeric Data Types
Date and Time Data Types
String Data Types
Other Data Types

Question 8

What are the SQL DML !Statements! that Query Databases and Modify Data in the Tables?

Answer 8

INSERT - Adding data to a relation
UPDATE - Modifying data in a relation
DELETE - Deleting data in a relation

Question 9

In the SQL Query Framework, what are the 3 Clauses and what do they do?

Answer 9

SQL SELECT - Specifies which columns are to be listed in the query results
SQL FROM - Specifies which tables are to be used in the query
SQL WHERE - Specifies which rows are to be listed in the query results

Question 10

What is the DISTINCT keyword in a SELECT statement’s effect?

Answer 10

It brings back all specific values in the query

Question 11

What are some of the SQL Comparison Operators? (Part 1)

Answer 11

<> = is NOT equal to (!= also applicable)
IN = Equal to one of a set of values
NOT IN = Not equal to any of a set of values
BETWEEN = Within a range of numbers
LIKE = Matches a set of characters
IS NULL = Is equal to NULL

Question 12

What is the ORDER BY clause with DESC/ASC Keywords?

Answer 12

By default, SQL Server sorts in ascending order, so ordering in descending order would be “ORDER BY TableName DESC”

Question 12

What is the ORDER BY clause with DESC/ASC Keywords?

Answer 12

By default, SQL Server sorts in ascending order, so ordering in descending order would be “ORDER BY TableName DESC”

Question 13

What are the 3 Options for the SQL Where Clauses?

Answer 13

Compound clauses (Use logical operators such as AND, OR, NOT)
Ranges (Use range of values like <, <=, >=)
Wildcards (Use underscores for each space needed or percent % signs for multiple spaces)

Question 14

How do you Include or Exclude Rows that contain NULL values?

Answer 14

Use the IS NULL or IS NOT NULL comparison values

Question 15

What are some of the SQL Built-In Aggregate Functions?

Answer 15

COUNT - Count number of rows in the table
SUM - Sum of all values (numeric columns only)
AVG - Average of all values (numeric columns only)
MIN - Minimum value of all values
MAX - Maximum value of all values

Question 16

What is the GROUP BY clause in SQL and why is it useful?

Answer 16

The GROUP BY clause groups rows by common values. It’s useful to view the table in ordered groups, such as departments or students in a certain dormitory.

Question 17

How would you Structure a Query for GROUP BY with the HAVING clause?

Answer 17

SELECT ColumnName
FROM TABLE
GROUP BY Column
HAVING CLAUSE

It is like having a filter condition on the GROUP BY results

Question 18

What are the 2 Techniques for Querying Data from Multiple Tables?

Answer 18

SQL Subquery
SQL Join

Question 19

What is the Limitation to the Subquery Approach?

Answer 19

Subqueries only work if the results are coming from a single table. If we need to display data from two or more tables, then subqueries do not work.

Question 20

How do we Use the SQL Join technique?

Answer 20

Use the WHERE clause involving the PK of one table and the Foreign Key (FK) of another.
Use the JOIN ON Syntax: still involves the PK of one table and the FK of another. JOIN ON combines two or more tables together.

Question 21

What are the 3 Queries for Multiple Tables?

Answer 21

SQL Inner Joins (also known as SQL Equijoins)
SQL Left Outer Joins
SQL Right Outer Joins

Question 22

How do SQL Inner Joins (SQL Equijoins) work?

Answer 22

An SQL WHERE clause is added to select only those rows where the primary key matches the foreign key. It requires that the values in the two columns are equal to each other.

Question 23

How do SQL Left Outer Joins and SQL Right Outer Joins work and differ from SQL Equijoins?

Answer 23

In SQL Left Outer and Right Outer Joins, the query will show the full data from one of the selected tables, including the rows that do not have a matching value in the PK/FK. Outer joins will produce NULL values in the non-matching rows of the unselected table.

Question 24

What are the 3 SQL DML !Commands! for Modifying and Deleting data from a DB? (Slightly different than the DML Statements)

Answer 24

Insert
Modify
Delete

Question 25

What are the SQL DDL Statements?

Answer 25

The DROP TABLE
The ALTER TABLE
When altering a table, you can DROP constraints or other rules