SQL Career Principles Flashcards
(31 cards)
What are the six main data types in SQL?
- INT - integer (whole number)
- DECIMAL (x,x) - Decimal numbers (first x states digits, second x states digits after decimal)
- VARCHAR(x)- string of text(x is used to specify max length)
- DATE - in YYYY-MM-DD format
- TIMESTAMP - in YYYY-MM-DD HH:MM:SS
- BLOB - Binary Large Object. Used for files, images, videos, and other large projects
What is the SQL syntax to create a database?
CREATE DATABASE name
name = database name
or click the “Create New Schema” button
What is the SQL syntax to create a table?
CREATE TABLE name (
column name1 INT PRIMARY KEY,
column name2 VARCHAR(25),
column name3 DEC(8,2)
)
name = table name; column name = name of column, for each column you need to identify the data type and also indicate the primary key
What does first_name VARCHAR(25) mean?
this is a column name that is a text data type limited to 25 characters
what does salary DEC(8,2) mean?
this is a column name and this data type is used for decimal numbers. The “8” represents the number of total digits and the “2” represents the number of decimals. In this case a $100K salary would show as 100000.00
How do you make a change to a table or column after creating?
To make a change to a table:
ALTER TABLE database.tablename ADD column4 INT(2) this will add another column that is a whole number with max 2 characters
How to change the name of a table ?
RENAME TABLE tablename TO tablename2
write the current table name first and the new table name second
How to change the data type for a column?
ALTER TABLE tablename MODIFY COLUMN column2 VARCHAR(50)
this will change column2 to a text field with a 50 character max
NEED TO CONFIRM THIS
How to add data to the tables?
most of the time you will add data via CSV or API but you can add manually by:
INSERT INTO tablename VALUES (Column1data, Column2data, Column3data)
How to change data in a table?
UPDATE tablename
SET column1 = ‘bbbb’
WHERE column1 = ‘aaaa’
*this changes any ‘aaa’ in column1 to ‘bbbb’
*** if you want to change all the data within a column to the same text/number/date you just delete the WHERE statement
how to run a query for the top 10 records?
simply use the LIMIT 10 statement
Let’s say you have a column lif_ expectancy and you want to create another column that adds 10 years to each record for life_expectancy, how would you do this?
Select
life_expectancy,
life_expectancy +10
FROM tablename
What is the BETWEEN statement and what is the syntax?
the BETWEEN statement is used with WHERE or HAVING. this will filter the records based on a range of values
SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN lower_bound
AND upper_bound;
What is REGEXP?
this is a statement used with WHERE or HAVING that is used for pattern matching. It is similar to LIKE but more powerful
examples:
match strings starting with a specific letter: WHERE name REGEXP ‘^A’
match strings ending with a specific letter:
WHERE name REGEXP ‘N$’
match strings containing digits: where product_number REGEXP ‘[0-9]’
match multiple patterns: WHERE fruit_name REGEXP ‘apple|orange’
| = or
You can write WHERE name REGEXP ‘^a[cdf]’ – this will return any text that starts with an ‘a’ and either has a c,d,f as the second character.
You can write WHERE name REGEXP ‘^a[b-f]’ – this will return any text that starts with an ‘a’ and either has a b,c,d,e,f as a second character
What are window functions in SQL?
a special type of function that perform calculations across a set of rows that are related to the current row. However, unlike aggregate functions (like SUM, AVG, etc.), window functions do not group the rows into a single result—they retain the individual rows while providing a calculated value based on the defined “window” of data
this statement goes in the SELECT section and the syntax is:
function_name (column_name) OVER (
[PARTITION BY column_name(s)]
[ORDER BY column_name(s)]
)
There are several types of functions you can use.
1. Aggregate
2. Value
3. Ranking
Related to the Window function, explain the different functions you can use
Aggregate:
-Avg()
-Max()
-Min()
-Sum()
-Count
Value:
-Row_number
-RANK()
-DENSE_RANK()
-PERCENT_RANK()
-NTILE()
Ranking:
-LAG()
-LEAD()
-FIRST_VALUE()
-LAST_VALUE()
-NTH_VALUE()
Related to the Window function, what is the LAG & LEAD function?
Lag Function “look behind function”:
- What it does: It retrieves the value from the previous row in a result set.
- Simple Example: Imagine a list of dates and sales. If you want to know what the sales were on the previous day for each row, you use the LAG function.
Lead Function look ahead function:
- What it does: It retrieves the value from the next row in a result set.
- Simple Example: If you want to know what the sales will be on the next day for each row, you use the LEAD function.
Related to the Window function, what is the difference between ROW_NUMBER, RANK, and DENSE_RANK?
- ROW_NUMBER
What it does: Assigns a unique, sequential
number to each row in the result set.Key feature: No ties—each row gets a unique
number, even if two or more rows have the same
values in the columns being ordered. - RANK
What it does: Assigns a rank to each row, but
rows with the same values get the same rank.However, it skips the next rank(s) after a tie. - DENSE_RANK
What it does: Similar to RANK, but it doesn’t skip
ranks after a tie.
How to join two different databases?
Very similar to joining two tables, just add the database name (note world and company are two different databases):
SELECT * FROM world.city
INNER JOIN company.teams
ON city.id = teams.staff_id
What are some of the top STRING functions?
- LENGTH: Returns the string length
LENGTH(string) - LEFT / RIGHT: Returns the left n characters / right
n characters of a string
LEFT(string, n) / RIGHT(string, n) - REPLACE: Replaces all occurrences of a specified
string value with another specified string value
REPLACE(string, start, length, replacement) - UPPER / LOWER: Changes the format to
uppercase / lowercase
UPPER(string) / LOWER(string) - TRIM: Removes the space character or other
specific character from the start / end of a string
TRIM(string) - CONCAT: Returns a string after joining two or
more string
values
CONCAT(string1, string2, …) - SUBSTRING: Extracts a substring from a string
SUBSTRING(string, start, length)
What are some data & time functions?
- NOW: Returns the current date and time - NOW()
- CURTIME: Returns the current time - CURTIME()
- CURDATE: Returns the current date - CURDATE()
- DAY: Returns the day - DAY(date)
- MONTH: Returns the month- MONTH(date)
- YEAR: Returns the year - YEAR(date)
- DAYNAME: Returns the name of the day -
DAYNAME(date) - MONTHNAME: Returns the name of the month -
MONTHNAME(date) - WEEK: Returns the week number - WEEK(date)
- DATEFORMAT: Formats the date as you specify -
DATE_FORMAT(date, format)
How do you write an IF statement?
it is pretty similar to excel
IF(salary >= 50000,’large_salary’,’small salary’)
Notes:
-when using text, make sure to use quotes
What is the CASE statement and how do you use it?
this is used in the SELECT section and is it similar to the IF function but more powerful
CASE
WHEN indepYear = 0 THEN ‘never’
WHEN indepYear < 1900 THEN ‘ancient
else ‘new’
END AS category
make sure you use END for the statement to work
What is the CAST statement and how do you use it?
The CAST statement in SQL is like a tool that changes the “type” of data so it fits what you need. Imagine you’re working with numbers, text, or dates, and you need to switch one type into another to do something specific. That’s where CAST helps.
you would use this to change a decimal data type to change an Integer (note you need to use the SIGNED function). see below:
SELECT
lifeexpectancy,
CAST(lifeexpectancy AS SIGNED)
FROM country;
