DATABASE

Question 1

WHAT IS A ROW IN A DATABASE?

Answer 1

record

Question 2

WHAT IS A COLUMN IN A DATABASE?

Answer 2

field

Question 3

SELECT *
FROM country;

Answer 3

get all columns from country table

Question 4

SELECT id, name
FROM city;

Answer 4

fetches id and name columns from city table

Question 5

SELECT name
FROM city
ORDER BY rating (ASC);

Answer 5

selects name column from city table and sorts it by rating in ascending order

Question 6

SELECT name
FROM city
ORDER BY rating (DESC);

Answer 6

selects name column from city table and sorts it by rating in descending order

Question 7

SELECT name
FROM city
WHERE rating > 3;

Answer 7

fetch names from city table where the rating is above 3

Question 8

SELECT name
FROM city
WHERE name != ‘Berlin’
AND name != ‘Madrid’;

Answer 8

fetch names from city table that are neither Berlin or Madrid

Question 9

SELECT name
FROM city
WHERE name LIKE ‘P%’
OR name LIKE ‘%s’ ;

Answer 9

fetch names of cities where their names start with P or ends in S

Question 10

SELECT name
FROM city
WHERE name LIKE ‘_ublin’;

Answer 10

fetch names of cities with any letter followed by ‘ublin’

Question 11

SELECT name
FROM city
WHERE population BETWEEN 50000 AND 5000000;

Answer 11

select names of cities where population is between 50K and 5M

Question 12

SELECT name
FROM city
WHERE rating IS NOT NULL;

Answer 12

fetch names of cities where their rating does not miss a value

Question 13

SELECT name
FROM city
WHERE country_id IN (1, 4, 7, 8);

Answer 13

fetch names of cities that are in countries with IDs 1, 4, 7, and 8

Question 14

WHAT DOES “INNER JOIN” do?

SELECT city.name, country.name
FROM city
[INNER] JOIN country
ON city.country_id = country.id;

Answer 14

returns rows that have matching values in both tables

ex.
SELECT city.name, country.name
FROM city
INNER JOIN country
ON city.country_id = country.id;

Question 15

WHAT DOES “LEFT JOIN” DO?

SELECT city.name, country.name
FROM city
LEFT JOIN country
ON city.country_id = country.id;

Answer 15

returns all rows from the left table with corresponding rows from the right table

if there’s no matching row, NULLs are returned as values from the second table

Question 16

WHAT DOES “RIGHT JOIN” DO?

SELECT city.name, country.name
FROM city
RIGHT JOIN country
ON city.country_id = country.id;

Answer 16

returns all rows from the right table with corresponding rows from the left table

if there’s no matching row, NULLs are returned as values from the left table

Question 17

WHAT DOES “FULL JOIN” DO?

SELECT city.name, country.name
FROM city
FULL JOIN country
ON city.country_id = country.id;

Answer 17

returns all rows from both tables

Question 18

WHAT DOES “CROSS JOIN” do?

SELECT city.name, country.name
FROM city
CROSS JOIN country;

SELECT city.name, country.name
FROM city, country;

Answer 18

returns all possible combinations of rows from both tables

Question 19

WHAT DOES “NATURAL JOIN” DO?

SELECT city.name, country.name
FROM city
NATURAL JOIN country;

Answer 19

joins tables by all columns with the same name

Question 20

WHAT DOES “ALTER TABLE” DO?

ALTER TABLE table_name
ADD column_name datatype;

Answer 20

allows you to add columns to a table in database

Question 21

WHAT DOES “AS” DO?

SELECT column_name AS ‘ALIAS’
FROM table_name;

Answer 21

allows you to rename a column or table using an alias