Week 7

Question 1

What is the UNIQUE Constraint in MySQL?

Answer 1

Ensures values in a column or group of columns are different

Question 2

What is the CHECK Constraint in MySQL?

Answer 2

Ensures that values stored in a column or group of columns satisfy a Boolean expression

Question 3

What is the NOT NULL Constraint in MySQL?

Answer 3

Ensures values in a column or a group of columns are different

Question 4

What is a Foreign Key (Constraint)?

Answer 4

Refers to Primary Key in another table

Is a column or set of columns used to establish a link between the data in two tables

These columns must match the columns in the referenced table (the parent table) and must have the same data type

Question 5

What are Constraints in MySQL?

Answer 5

SQL Constraints are rules applied to columns in a table to ensure data integrity and consistency

They help to define the type of data that can be inserted into a table and prevent invalid data

Question 6

What is a Primary Key (Constraint)?

Answer 6

Implicitly means NOT NULL + UNIQUE

A table can have one and only one primary key

Question 7

What is SQL?

Answer 7

SQL is a standard language for interaction with a relational database

declarative language (focuses on the what, not how)

Question 8

What is SELECT in SQL?

Answer 8

The SQL command used to retrieve data from a database

It specifies which columns and rows to fetch from one or more tables. Example: SELECT column1, column2 FROM table_name;

Question 9

What is SELECT- DISTINCT in SQL?

Answer 9

The SQL command used to retrieve unique (non-duplicate) values from a column or set of columns in a table

It removes duplicate rows from the result

Example: SELECT DISTINCT column1 FROM table_name;

Question 10

What is SELECT - WHERE in SQL?

Answer 10

The SQL command used to retrieve data from a table based on a specific condition

The WHERE clause filters the rows that meet the given condition

Example: SELECT column1 FROM table_name WHERE condition;

This returns only the rows where the condition is true

eg.

SELECT CustomerName, City, Country
FROM Customers
WHERE Country = ‘Spain’

Question 11

What are the 5 basic Selection conditions in SQL?

Answer 11

Comparison: =, <>, (!=), <, <=, >=, >, OR, AND, NOT

Range: BETWEEN … AND, NOT BETWEEN.. AND

Set membership: IN, NOT IN

Pattern Match: LIKE, NOT LIKE

IS NULL, IS NOT NULL

Question 12

What is SELECT - BETWEEN/IN

Answer 12

SELECT - BETWEEN/IN:

BETWEEN: Filters rows where a column value is within a specified range (inclusive).

Example: SELECT column1 FROM table_name WHERE column1 BETWEEN 10 AND 20;

IN: Filters rows where a column value matches any value in a specified list.

Example: SELECT column1 FROM table_name WHERE column1 IN (10, 20, 30);

Question 13

What is SELECT - LIKE/NOT LIKE

Answer 13

SELECT - LIKE/NOT LIKE:

LIKE: Filters rows where a column value matches a specified pattern, often using wildcard characters (% for any sequence of characters, _ for a single character)

Example: SELECT column1 FROM table_name WHERE column1 LIKE ‘A%’; (finds values starting with “A”)

NOT LIKE: Filters rows where a column value does not match a specified pattern

Example: SELECT column1 FROM table_name WHERE column1 NOT LIKE ‘A%’; (finds values that don’t start with “A”)

Question 14

What is SELECT - IS NULL/ IS NOT NULL

Answer 14

IS NULL: Filters rows where a column value is NULL (unknown or missing).

Example: SELECT column1 FROM table_name WHERE column1 IS NULL;

IS NOT NULL: Filters rows where a column value is not NULL (i.e., has a value).

Example: SELECT column1 FROM table_name WHERE column1 IS NOT NULL;

Question 15

What is SELECT - ORDER BY

Answer 15

SELECT - ORDER BY: The SQL command used to sort the results of a query by one or more columns, either in ascending (ASC) or descending (DESC) order.

Example:

SELECT column1 FROM table_name ORDER BY column1 ASC; (sorts in ascending order)

SELECT column1 FROM table_name ORDER BY column1 DESC; (sorts in descending order)

Question 16

What are the 5 Aggregate Functions?

Answer 16

COUNT

MIN

MAX

SUM

AVG

Question 17

What does COUNT do?

Answer 17

An SQL aggregate function that returns the number of rows that match a specified condition or the total number of rows in a table.

Example:
SELECT COUNT(*) FROM table_name; (counts all rows in the table)

SELECT COUNT(column1) FROM table_name WHERE condition; (counts rows where column1 is not NULL)

Question 18

What is MIN and MAX in SQL?

Answer 18

MIN and MAX: SQL aggregate functions used to find the smallest (MIN) and largest (MAX) values in a specified column.

MIN: Returns the smallest value in a column.

Example: SELECT MIN(column1) FROM table_name;

MAX: Returns the largest value in a column.

Example: SELECT MAX(column1) FROM table_name;

Question 19

What is SUM in SQL?

Answer 19

An SQL aggregate function that returns the total sum of values in a numeric column

Example:

SELECT SUM(column1) FROM table_name;

This adds up all the values in column1.

Question 20

What is AVG in SQL?

Answer 20

An SQL aggregate function that calculates the average (mean) value of a numeric column

Example:

SELECT AVG(column1) FROM table_name;

This returns the average of all values in column1.

Question 21

What is GROUP BY in SQL?

Answer 21

GROUP BY in SQL is used to organize data into groups based on one or more columns.

It aggregates rows that have the same values in specified columns, often used with aggregate functions like SUM, AVG, COUNT, etc

Question 22

What is a Subquery (or nested query)?

Answer 22

A complete SELECT statement embedded in another query