SQL

Question 1

What is a foreign key?

Answer 1

A foreign key is a column or group of columns in a relational database table that refers to the primary key of another table.

Question 2

What is schema?

Answer 2

• a collection of Tables, which can be split and grouped according to logic.
• In MySQL, Schema and Database are integrated into one.

Question 3

Command to display table column info.

Answer 3

SHOW FULL COLUMNS FROM table_name;

Question 4

Command to delete table

Answer 4

DROP TABLE table_name;

Question 5

Delete data in table (keep the table).

Answer 5

TRUNCATE table_name;

Question 6

what are the number data types in SQL?

Answer 6

1. BIGINT, INT, MEDIUMINT, SMALLINT, TINYINT
2. DOUBLE, FLOAT, DECIMAL

Question 7

What are the text types in SQL?

Answer 7

CHAR, VARCHAR (variable length –> VARCHAR(45)), can be set, TEXT, LONGTEXT

Question 8

what are date time data types in SQL?

Answer 8

DATE, MONTH, YEAR, DATETIME , TIMESTAMP (limited to a range between 1970-01-01 00:00:01 and 2038-01-19 03:14:07)

Question 9

Add column ‘age’ to table ‘users’ with data type INT and null values allowed. Insert after column ‘name’.

Answer 9

ALTER TABLE users
ADD COLUMN age INT NULL AFTER name;

Question 10

Command to change existing column.

Answer 10

ALTER TABLE new_schema.users
CHANGE COLUMN id id INT(11) NOT NULL AUTO_INCREMENT,
CHANGE COLUMN name user_name VARCHAR(45) NOT NULL DEFAULT ‘No Name’;

Question 11

Command to display all data in a table

Answer 11

SELECT * FROM <table_name>;</table_name>

Question 12

Show all data for user with id=2

Answer 12

SELECT * FROM users WHERE id= 2;

Question 13

What is the difference between WHERE and HAVING clauses?

Answer 13

the WHERE clause is used to filter records before they are grouped and aggregated, while the HAVING clause is used to filter groups after they have been formed.

SELECT * FROM customers
WHERE city = ‘New York’;
########
SELECT city, COUNT() as count
FROM customers
GROUP BY city
HAVING COUNT() > 2;

Question 14

example query to find all occurrences of error code 403

Answer 14

SELECT system_number, DATE(date_time) as error_date, COUNT(*) as error_count
FROM table_name
WHERE error_code = 403
GROUP BY system_number, error_date;

Question 15

Types of constraints in SQL

Answer 15

NOT NULL Constraint: This constraint ensures that a column cannot contain null values. It forces the column to have a value for every row.
UNIQUE Constraint: This constraint ensures that each value in a column is unique, meaning that no two rows can have the same value in that column.
PRIMARY KEY Constraint: This constraint is a combination of NOT NULL and UNIQUE constraints. It ensures that a column or a group of columns uniquely identifies each row in a table.
FOREIGN KEY Constraint: This constraint ensures that the values in a column or a group of columns in one table match the values in another table’s primary key column(s).
CHECK Constraint: This constraint ensures that a value in a column meets a specific condition, such as a range of values, a regular expression, or a list of allowed values.
DEFAULT Constraint: This constraint sets a default value for a column when no value is provided.

Question 16

What is the difference between GROUP BY and ORDER BY clauses?

Answer 16

GROUP BY is used to group the result set and perform calculations on the data (typically used with aggregate functions, such as COUNT(), SUM(), AVG(), and MAX()) , while ORDER BY is used to sort the result set in a specific order.

Question 17

query that groups the result set by the “department” column and calculates the total salary for each department

Answer 17

SELECT department, SUM(salary) as total_salary
FROM employees
GROUP BY department;

Question 18

sort employees in ‘employees’ table by the “last_name” column in descending order:

Answer 18

SELECT *
FROM employees
ORDER BY last_name DESC;