SQL Basic

Question 1

SQL and MySQL

Answer 1

SQL Structured Query Language

MySQL Database management sysytem-DBMS, other are PostgreSQL, SQLite, Oracle, There are slight differences in the syntax. Implement SQL standard

Question 2

changing/selecting database

Answer 2

Use Database_name

Question 3

to check what db i am using

Answer 3

Select Database_Name()

Question 4

To drop a database:

Answer 4

DROP DATABASE <database-name>;</database-name>

Question 5

To use a database:

Answer 5

USE <database-name>;</database-name>

Question 6

To list available databases:

Answer 6

show databases;

Question 7

The general command for creating a database:

Answer 7

CREATE DATABASE <database_name>;</database_name>

CREATE DATABASE soap_store;

Question 8

2 main data types

Answer 8

numeric and string

Question 9

show tables;

Answer 9

show columns from Table1;
DESC Table1;

Question 10

Insert a cat into a table

Answer 10

INSERT INTO cats (name, age)
VALUES (‘Blue Steele’, 5);

Question 11

Primary key

Answer 11

means unique,
auto-increment

cat_id INT PRIMARY KEY
at the begging or
PRIMARY KEY (cat_id)
at the end

Question 12

Update example

Answer 12

UPDATE cats SET breed=’Shorthair’ WHERE breed=’Tabby’;

using SET and where

Question 13

What to use before Deleting/Updating

Answer 13

Select before UPDATE/ DELETE!

Question 14

String functions

Answer 14

1. concat
2. substring
3. replace
4. reverse
5. char_length
6. upper&lower

using select

SELECT CONCAT(author_fname,’ ‘, author_lname) AS author_name FROM books;
SELECT SUBSTRING(‘Hello World’, 1, 4);

Question 15

Refining selections

Answer 15

distinc- only unique values -> Select Distinct

SELECT DISTINCT author_lname FROM books;

Question 16

Order by example

Answer 16

SELECT author_lname FROM books ORDER BY author_lname;
ASCENDING By Default

Question 17

Limit example

Answer 17

SELECT title, released_year FROM books
ORDER BY released_year DESC LIMIT 5;

Question 18

LIKE

Answer 18

Better searching

WHERE author_fname LIKE ‘%da%’
WILDCARDS

Question 19

Aggregate functions

Answer 19

Count
Group By
Min/Max
Sum
Avg

Question 20

Count example

Answer 20

SELECT COUNT(*) FROM books;

SELECT COUNT(author_fname) FROM books;

SELECT COUNT(DISTINCT author_fname) FROM books;

SELECT COUNT(*) FROM books
WHERE title LIKE ‘%the%’;

Question 21

GROUP BY

Answer 21

GROUP BY summarizes or aggregates identical data into single rows

Question 22

Data types- String data types

Answer 22

char- fixed in size length, for text of similar length
varchar- text of different sizes
nvarchar- 1 character takes 2 bytes, max length is 50

Question 23

Data types- numeric

Answer 23

tinyint- from -127 to 128
smallint- from -32k to +32k
mediumint- from -8 milion to + 8 milion
int- from -2b to +2b

Question 24

Data types- decimal

Answer 24

decimal- (total number of digits (maxium/ up to), digits after decimal)
float- not as precise as double
double- very precise, dobule as precise

Question 25

Constraints

Answer 25

Not null unique Check Alter table

Question 26

Named constraints

Answer 26

CREATE TABLE partiers2 ( name VARCHAR(50), age INT, **CONSTRAINT** age_over_18 **CHECK** (age > 18) );

Question 27

Primary key, foreign key

Answer 27

Primary key- it is always unique, not null, to identify in a table Foreign key- refernces to another table within another table

Question 28

INNER JOIN

Answer 28

Select all records from A and B where the join condition is met SELECT * FROM customers JOIN orders ON customers.id = orders.customer_id;

Question 29

LEFT JOIN

Answer 29

Select everything from A, along with any matching records in B

Question 30

RIGHT JOIN

Answer 30

Select everything from B, along with any matching records in A

Question 31

On delete cascade

Answer 31

ON DELETE CASCADE at the end at the table so when i delete the data that are being referenced it gets deleted

Question 32

View

Answer 32

**a reusable query** CREATE VIEW full_reviews AS SELECT * FROM ...

Question 33

Having

Answer 33

specify **condition** on groups, typically formed using Group By clause GROUP BY title HAVING COUNT(rating) > 1;

Question 34

Roll up

Answer 34

works with Group by, make a statistic in the last row-average, count...

Question 34

Modes

Answer 34

-setting of current sql -- To View Modes: SELECT @@GLOBAL.sql_mode; SELECT @@SESSION.sql_mode; -- To Set Them: SET GLOBAL sql_mode = 'modes'; SET SESSION sql_mode = 'modes';

Question 35

Window Function

Answer 35

make a Grupy By function, but show the result next to every row, it doesnt reduce the amout of rows