sql test 03.11.26

0.0(0)
Studied by 19 people
call kaiCall Kai
learnLearn
examPractice Test
spaced repetitionSpaced Repetition
heart puzzleMatch
flashcardsFlashcards
GameKnowt Play
Card Sorting

1/110

flashcard set

Earn XP

Description and Tags

theory + syntax

111 Terms

1
New cards
SQL
structured query language, the standard language for relational databases
2
New cards
RDBMS
relational database management system, manages data stored in tables
3
New cards
MySQL
an RDBMS with a GUI (Workbench) and CLI that uses its own dialect of SQL
4
New cards
GUI vs CLI
graphical interface (MySQL Workbench) vs text command line
5
New cards
;
ends an SQL query
6
New cards
case sensitivity
SQL keywords aren't case sensitive, but CAPITALS for keywords is best practice
7
New cards
naming convention
lowercase snake_case (student_no), because SQL isn't case sensitive and a name must be one identifier
8
New cards
*
all columns
9
New cards
DDL
data definition language: CREATE, ALTER, DROP
10
New cards
DML
data manipulation language: INSERT, UPDATE, DELETE
11
New cards
DQL
data query language: SELECT
12
New cards
CRUD
create INSERT, read SELECT, update UPDATE, delete DELETE
13
New cards
CHAR(n)
fixed length string
14
New cards
VARCHAR(n)
variable length string, max n
15
New cards
INT / FLOAT / DOUBLE
integer / real / double precision real
16
New cards
BOOLEAN
stored as TINYINT(1), FALSE = 0, TRUE = 1
17
New cards
ENUM
choose 1 value from a list of strings
18
New cards

DATE / TIME / DATETIME format

DATE format - 'YYYY-MM-DD', e.g. '2024-03-23'

TIME format - 'hh:mm:ss', e.g. '23:05:59'

DATETIME format - 'YYYY-MM-DD hh:mm:ss', e.g. '2024-03-23 23:05:59'

19
New cards
constraints
rules for allowed values that keep data integrity
20
New cards
data validation
done with CHECK (and NOT NULL, UNIQUE, ENUM)
21
New cards
UNIQUE
no duplicate values in the column
22
New cards
NOT NULL
a value is required
23
New cards
DEFAULT
value used if none is given
24
New cards
CHECK
validation condition the value must pass
25
New cards
primary key
unique + not null, identifies each record
26
New cards
AUTO_INCREMENT
PK generated automatically, can be skipped in INSERT
27
New cards
MODIFY COLUMN rule
repeat all properties and constraints or you lose them
28
New cards
adding a NOT NULL column
also give a DEFAULT value
29
New cards
adding a constraint with ALTER
denied if existing data breaks it
30
New cards
INSERT error
inserting only some values without a column list before VALUES
31
New cards
safe mode
blocks UPDATE/DELETE without WHERE on the PK
32
New cards
UPDATE/DELETE without WHERE
affects all rows, needs safe mode off
33
New cards
comparison operators
=, != or <>, >, >=, <, <=
34
New cards

logical operators

AND (&&), OR (||), NOT (!), XOR

<p>AND (&amp;&amp;), OR (||), NOT (!), XOR</p>
35
New cards
!= and <>
synonyms in MySQL
36
New cards
IS NULL
correct way to check for NULL (not = NULL)
37
New cards
BETWEEN
range including the border values
38
New cards
IN
matches a set of values, replaces many ORs
39
New cards
LIKE %
any number of characters
40
New cards
LIKE _
exactly one character
41
New cards
DISTINCT
removes duplicate rows/combinations
42
New cards
LIMIT N
first N records
43
New cards
ORDER BY
sort, ASC (default) or DESC
44
New cards
aggregate functions
SUM, AVG, MIN, MAX, COUNT; turn a whole column into one value
45
New cards
COUNT(col) vs COUNT(*)
COUNT(col) skips NULLs, COUNT(*) counts all rows
46
New cards
NULL in aggregates
ignored
47
New cards
DELETE vs DROP
DELETE removes rows, DROP removes the whole table or database
48
New cards
show all databases
SHOW DATABASES;
49
New cards
choose database school
USE school;
50
New cards
show all tables in the current db
SHOW TABLES;
51
New cards
show the columns and types of students
DESC students;
52
New cards
create database school
CREATE DATABASE school;
53
New cards
create table students (name text 30, age integer, grade real)
CREATE TABLE students(name VARCHAR(30), age INT, grade FLOAT);
54
New cards
create table with an auto PK id
CREATE TABLE students(id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(30));
55
New cards
delete table students
DROP TABLE students;
56
New cards
delete database school
DROP DATABASE school;
57
New cards
add column student_no as the first column
ALTER TABLE students ADD COLUMN student_no INT FIRST;
58
New cards
add column email after name
ALTER TABLE students ADD COLUMN email VARCHAR(50) AFTER name;
59
New cards
change student_no to unique and required
ALTER TABLE students MODIFY COLUMN student_no INT UNIQUE NOT NULL;
60
New cards
delete column age
ALTER TABLE students DROP COLUMN age;
61
New cards
name is required
name VARCHAR(30) NOT NULL
62
New cards
student_no can't repeat
student_no INT UNIQUE
63
New cards
grade defaults to 2.00
grade FLOAT DEFAULT 2.00
64
New cards
grade must be between 2 and 6
grade FLOAT CHECK(grade >= 2 AND grade <= 6)
65
New cards
age can't be negative
age INT CHECK(age >= 0)
66
New cards
country code 2 letters, default BG
country_code CHAR(2) DEFAULT 'BG'
67
New cards
grade with 3 digits total, 2 after the point
grade FLOAT(3,2)
68
New cards
column that is true/false, default false
senior BOOLEAN DEFAULT FALSE
69
New cards
gender can only be M or F
gender ENUM('M', 'F')
70
New cards
registration date defaults to today
registration_date DATE DEFAULT NOW()
71
New cards
last updated auto timestamp
last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP
72
New cards
convert datetime column to date
CAST(datetime_col AS DATE)
73
New cards

insert one student (all columns)

INSERT INTO students VALUES ('Katerina', 17, 5.50);

74
New cards

insert several students

INSERT INTO students VALUES ('Katerina', 17, 5.50), ('John', 16, 6.00);

75
New cards
insert only name and grade (rest NULL)
INSERT INTO students(name, grade) VALUES ('Siyka', 4.75);
76
New cards
select everything from students
SELECT * FROM students;
77
New cards
select only name and age
SELECT name, age FROM students;
78
New cards
rename column name to Student in the output
SELECT name AS Student FROM students;
79
New cards
alias with a space
SELECT AVG(grade) AS 'average grade' FROM students;
80
New cards
students aged 17
SELECT * FROM students WHERE age = 17;
81
New cards
age 17 and grade at least 5.50
SELECT * FROM students WHERE age = 17 AND grade >= 5.50;
82
New cards
age not equal to 17
SELECT * FROM students WHERE age != 17; (or <> 17)
83
New cards
students with no grade
SELECT * FROM students WHERE grade IS NULL;
84
New cards
students that have a grade
SELECT * FROM students WHERE grade IS NOT NULL;
85
New cards
grade from 5 to 6 including both
SELECT * FROM students WHERE grade BETWEEN 5 AND 6;
86
New cards
name is Dancho, Pancho or Gancho
SELECT * FROM students WHERE name IN ('Dancho', 'Pancho', 'Gancho');
87
New cards
name starts with Da
SELECT * FROM students WHERE name LIKE 'Da%';
88
New cards
name ends with cho
SELECT * FROM students WHERE name LIKE '%cho';
89
New cards
name contains an
SELECT * FROM students WHERE name LIKE '%an%';
90
New cards
name has exactly 5 letters starting with S
SELECT * FROM students WHERE name LIKE 'S____';
91
New cards
third letter is n
SELECT * FROM students WHERE name LIKE '__n%';
92
New cards
subquery in IN
SELECT name FROM students WHERE name IN (SELECT name FROM students WHERE name LIKE '%cho');
93
New cards

change John’s age to 17

UPDATE students SET age = 17 WHERE name = 'John';

94
New cards
update two columns
UPDATE students SET name = 'X', age = 18 WHERE student_no = 10001;
95
New cards

delete John

DELETE FROM students WHERE name = 'John';

96
New cards
delete all records (table stays)
DELETE FROM students;
97
New cards
unique names only
SELECT DISTINCT name FROM students;
98
New cards
unique name + grade combinations
SELECT DISTINCT name, grade FROM students;
99
New cards
count unique names
SELECT COUNT(DISTINCT name) FROM students;
100
New cards
first 3 records
SELECT * FROM students LIMIT 3;