SQL

Question 1

What does sql stand for

Answer 1

Structured Query Language

Question 2

What is a relational database

Answer 2

A database that stores data in tables (rows + columns) with relationships via keys.

Question 3

How do you create a database in SQL?

Answer 3

CREATE DATABASE dbname;

Question 4

What does dropping a database do? DROP dbname

Answer 4

Permanently deletes the entire database and all tables.

Question 5

What is a table in SQL

Answer 5

A structure of rows (records) and columns (fields)

Question 6

How do you create a table in SQL

Answer 6

CREATE TABLE tableName ( columns… );

Question 7

What does SELECT * do?

Answer 7

Selects all rows and all columns in a table

Question 8

How do you select specific columns in SQL

Answer 8

SELECT col1, col2 FROM tableName;

Question 9

What is the purpose of the where clause

Answer 9

Filters rows based on a condition.

Question 10

Give an example of using the where clause employees have an hourly_pay greater than equal to 15

Answer 10

SELECT *
FROM employees
WHERE hourly_pay >= 15;

Question 11

Name 3 SQL composition operators

Answer 11

=, != (or <>), <, >, >=, <=, IS NULL, IS NOT NULL.

Question 12

How do you insert one row into a table in SQL

Answer 12

INSERT INTO table VALUES (…);

Question 13

How do you insert multiple rows into a table in SQL

Answer 13

INSERT INTO table
VALUES (…),
(…),
(…);

Question 14

How do you insert only specific columns?

Answer 14

INSERT INTO table (col1, col2) VALUES (…);

Question 15

How do you update a single column in a table in SQL?

Answer 15

UPDATE table
SET col = value
WHERE condition;

Question 16

Why must every UPDATE or DELETE include a WHERE clause?

Answer 16

Without it, all rows in the table will be modified or removed.

Question 17

What is COMMIT used for?

Answer 17

Saves the current transaction permanently.

Question 18

What is ROLLBACK used for?

Answer 18

Undoes all changes since the last COMMIT.

Question 19

What do CURRENT_DATE(), CURRENT_TIME(), and NOW() return?

Answer 19

Today’s date, the current time, and current date + time.

Question 20

What does the UNIQUE constraint ensure?

Answer 20

All values in the column must be different.

Question 21

What does the NOT NULL constraint ensure?

Answer 21

The column cannot contain NULL values.

Question 22

show how to implement NOT NULL and UNIQUE into a new table

Answer 22

CREATE TABLE products(
product _id INT, product_name VARCHAR (25)UNIQUE AND NOTNULL,
price DECIMAL (4, 2)

Question 23

What does a check constraint too

Answer 23

Restricts what values are allowed in a column.

Question 24

Give an example of adding a names check constraint

Answer 24

*CREATE TABLE employees (
employee_id INT,
first_name VARCHAR (50),
last _name VARCHAR(50),
hourly_pay DECIMAL (5, 2),
hire_date DATE,

    CONSTRAINT chk hourly pay CHECK (hourly pay >= 10.00) );

Question 25

How do I add a check constraint when the table is already made

Answer 25

ALTER TABLE employees ADD CONSTRAINT chk_hourly_pay CHECK (hourly_pay ›= 10.00);

Question 26

What does default do

Answer 26

sets an automatic value when none is specified

Question 27

Show how default can be used

Answer 27

CREATE TABLE products ( product_id INT, product _name VARCHAR (25), price DECIMAL(4,2) DEFAULT 0 ):

Question 28

What are the two rules of a primary key

Answer 28

Must be unique and cannot be NULL

Question 29

How many primary keys can a table have

Answer 29

one (although it may consist of multiple columns (composite))

Question 30

What does AUTO_INCREMENT do

Answer 30

Automatically increases numeric primary key values each time a new row is inserted.

Question 31

Give an example of how auto_increment is used?

Answer 31

CREATE TABLE transactions ( transaction_id INT PRIMARY KEY AUTO_INCREMENT, amount DECIMAL (5, 2) ");

Question 32

How can you change the value auto-increment begins at

Answer 32

AUTO_INCREMENT = 1

Question 33

What is a foreign key

Answer 33

A column that refers to a primary key in another table, creating a relationship.

Question 34

What does a foreign key prevent

Answer 34

Deleting or modifying parent rows that would break the relationship.

Question 35

What does an INNER JOIN return

Answer 35

Only rows where both tables have matching values.

Question 36

What does LEFT JOIN return

Answer 36

All rows from the left table, plus matches from the right.

Question 37

What does right join return

Answer 37

All rows from the right table, plus matches from the left.

Question 38

What is an SQL view

Answer 38

A stored virtual table based on a SELECT query. It does not store its own data — it displays data from underlying tables.

Question 39

How do you create a view

Answer 39

CREATE VIEW viewName AS SELECT columns FROM table WHERE condition;

Question 40

How do you use a view?

Answer 40

SELECT * FROM viewName;

Question 41

How do you delete a view?

Answer 41

DROP VIEW viewName;

Question 42

What is a database index?

Answer 42

A structure that speeds up searches on a table, like an index in a book.

Question 43

When should you add an index?

Answer 43

When you frequently search, filter, or join by a specific column.

Question 44

What is the drawback of adding too many indexes?

Answer 44

Faster reads, but slower inserts/updates, because the index must be updated too.

Question 45

How do you create an index on a column?

Answer 45

CREATE INDEX idx_name ON tableName(column);

Question 46

how do you remove an index

Answer 46

DROP INDEX idx_name ON tableName;

Question 47

Give an example of a subquery

Answer 47

SELECT * FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);

Question 48

What can a subquery in the FROM clause be used for?

Answer 48

SELECT * FROM (SELECT * FROM employees WHERE hourlyPay > 20) AS highEarners;

Question 49

What does a SELECT-level subquery return?

Answer 49

SELECT name, (SELECT COUNT(*) FROM orders WHERE orders.customerID = customers.id) FROM customers;

Question 50

What is a correlated subquery?

Answer 50

SELECT name FROM employees e WHERE salary > ( SELECT AVG(salary) FROM employees WHERE department = e.department );