SQL Revamp Journey Week 1

Week 1 of SQL:

Recently, I have taken up a challenge to brush up my SQL skills right from the basics. It’s been a long time since I had my hands on structured data. Moreover, I wanted to revamp some core CS concepts these 2 months and thought it would be a great idea if I start with SQL and Database Management Systems. I manage to learn for at least 30 min daily and would like to summarise the topics I undertake,which I intend to post on every Sunday. So, here is my Week 1 recap:

I’m pretty late for the post, this should’ve been out on 9th January itself. Nevermind, I’ll make up for it in the coming few days.

Day 1- Introduction and History of SQL and Relational Databases

• Relational Databases — A form of database that stores and allows access to data elements that are linked and structured in tabular format.
• Codds 12 rules of Relational Database model
• SQL(Structured Query Language) — A Standard Language used to manipulate and retrieve data from Relational Databases.
• Enables a programmer or database administrator to do the following:

● Modify a database’s structure

● Change system security settings

● Add user permissions on databases or tables

● Query a database for information

● Update the contents of a database

• Theory behind SQL and need for it.

Day 2- Introduction to Query:

• Query can retrieve data using the keywords SELECT and FROM.
• With a SELECT * command, you can produce a wide statement that includes all tables, or you can reorganise or retrieve individual tables.
• The keyword DISTINCT restricts the output so that duplicate values in a column do not appear.

Day 3- Expressions, Conditions and Operators

• Expressions- Anything that returns a value. Expression types cover different data types such as String, Numeric, and Boolean.
• Conditions- Contained in the WHERE clause.
• Operators- Operators are the elements you use inside an expression to articulate how you want specified conditions to retrieve data. Operators fall into six groups: arithmetic, comparison, character, logical, set, and miscellaneous.
• Arithmetic-
• Plus(+): create a virtual or derived column by adding values to existing columns.
• Minus(-): change the sign of a number. subtract one column from another to create a virtual or derived column.
• Divide(/): divide one column from another to create a virtual or derived column.
• Multiply(*): multiply one column from another to create a virtual or derived column.
• Modulo(%): returns the integer remainder of the division operation.
• Note: Preserves precedence order
• Comparison-
• Equal(=): Used alone, the equal sign is a very convenient way of selecting one value out of many.
• Greater Than (>) and Greater Than or Equal To (>=)
• Less Than (<) and Less Than or Equal To (<=)
• Inequalities (< > or !=)
• Logical Operators:
• AND: the expressions on both sides must be true to return TRUE. If either expression is false, AND returns FALSE.
• OR: to sum up a series of conditions. If any of the comparisons is true, OR returns TRUE.
• NOT: the condition it applies to evaluates to TRUE, NOT make it FALSE and vice versa.
• Character Operators:
• LIKE
• Concatenation (||): || symbol concatenates two strings.
• Underscore (_)
• Set Operators:
• UNION: returns the results of two queries minus the duplicate rows.
• INTERSECT: returns only the rows found by both queries.
• MINUS: returns the rows from the first query that were not present in the second.
• Miscellaneous Operators IN and BETWEEN:

Day 4- Functions: Moulding the Data You Retrieve:

• Functions greatly increase your ability to manipulate the information retrieved using the basic functions of SQL.
• Aggregate Functions: COUNT, SUM, AVG, MAX, MIN, VARIANCE, STDDEV
• Date and Time Functions: ADD_MONTHS, LAST_DAY, MONTHS_BETWEEN, NEW_TIME, NEXT_DAY, SYSDATE
• Arithmetic Functions: ABS, CEIL and FLOOR, COS, COSH, SIN, SINH, TAN, and TANH, EXP, LN and LOG, MOD, POWER, SIGN, SQRT
• Character Functions: CHR, CONCAT, INITCAP, LOWER and UPPER, LPAD and RPAD, LTRIM and RTRIM, REPLACE, SUBSTR, TRANSLATE, INSTR, LENGTH
• Conversion Functions: TO_CHAR, TO_NUMBER
• Miscellaneous Functions: GREATEST and LEAST, USER

Day 5 — Clauses in SQL:

• WHERE: Using just SELECT and FROM, you are limited to returning every row in a table. With WHERE in your vocabulary, you can be more selective.
• STARTING WITH: STARTING WITH is an addition to the WHERE clause that works exactly like LIKE(<exp>%).
• ORDER BY: The ORDER BY clause gives you a way of ordering your results.
• GROUP BY
• HAVING: WHERE does not work with aggregate functions. HAVING enables you to use aggregate functions in a comparison statement, providing for aggregate functions what WHERE provides for individual rows.

Day 6 — Joining Tables

• A join combines all possible combinations of rows present in the selected tables. These new rows are then available for selection based on the information that one wants.

Day 7 — Sub Queries

• A subquery is a query whose results are passed as the argument for another query. Subqueries enable you to bind several queries together.
• EXISTS takes a subquery as an argument and returns TRUE if the subquery returns anything and FALSE if the result set is empty. Closely related to EXISTS are the keywords ANY, ALL, and SOME. ANY and SOME are identical in function.