MySQL

Question 1

Working with Command Line Client (if the MySQL workbench doesn’t work)

Answer 1

show databases;
use world;
show tables;
SELECT * FROM City LIMIT 10;

Question 2

Clients + Servers (store files and databases) located on the same network

Answer 2

LAN (local area network)
WAN (wide area network) means 2+ networks are in the same geographical area

Question 3

Primary key

Answer 3

• Cannot be Null
• One per table
• Unique identifier of row

Question 4

Unique key

Answer 4

• Can have one Null

Question 5

Datatypes

Answer 5

String - CHAR, VARCHAR
Integer - INT
Float - FLOAT
Date and time - DATE

Question 6

Select current date and time / date / time

Answer 6

SELECT NOW();
SELECT CURDATE();
SELECT CURTIME();

SELECT SQRT(25);

Question 7

Create a default database

Answer 7

USE sakila;
SELECT *
FROM actor;

Question 8

Display the column names only (trick)

Answer 8

SELECT *
FROM sakila.actor
WHERE 1 = 2;

Note, 1 is never equal to 2, so it won’t show any data except for the column names

Question 9

Rounding etc

Answer 9

SELECT amount,
ROUND(amount) Amnt,
ROUND(amount, 1) Amnt1,
FLOOR(amount) FloorAmnt,
CEILING(amount) CeilingAmnt
FROM sakila.payment;

Floor rounds down to the nearest integer.
Ceiling rounds up to the nearest integer.

Question 10

Concatination

Answer 10

SELECT CONCAT(first_name, ‘ ‘, last_name) AS FullName
FROM sakila.actor;

SELECT CONCAT(LEFT(first_name,1), ‘ ‘, LEFT(last_name,1)) AS FirstInitial
FROM sakila.actor;

Question 11

Length of the string

Answer 11

SELECT LENGTH(CONCAT(first_name, ‘ ‘, last_name) AS length
FROM sakila.actor;

Question 12

Selecting date format

Answer 12

SELECT DATE_FORMAT(last_update, ‘%m/%d/%Y’
FROM sakila.actor;

Question 13

Selecting only unique rows

Answer 13

SELECT DISTINCT first_name
FROM sakila.actor;

Question 14

Insert row/rows

Answer 14

INSERT INTO sakila.my_table (first_name, last_name, last_update)
VALUES (‘Pinal’, ‘Dave’, ‘2023-04-15’),
(‘Pinal2’, ‘Dave2’, ‘2023-04-16’);

Question 15

Update (change) row/rows

Answer 15

UPDATE sakila.actor
SET first_name = ‘Pinal’,
last_name = ‘Dave’
WHERE actor_id IN (3,5,6);

Question 16

DELETE

Answer 16

DELETE
FROM sakila.actor
WHERE actor_id = 1;