# Master SQL Introduction ## 1.1 Basics of Databases What are db's, diff types of databases, SQL, diff flavors of SQL and the types of queries. >[! Warning] 1.1 Takeaways >- DB's are a way to organize data >- DBMS's are what is used to CRUD the data within a DB >- Two types of DB's > - Relational (Tables, Columns) > - NoSQL >- SQL is used to CRUD data in a DBMS ### 1.1.1 Data in the modern world - almost everything in the modern world revolves around storing, retrieving, analyzing, and managing data in some way. - Examples: Twitter, Instagram traking posts - Banking tracking transactions - Ecommerce tracking product catalogs - Airlines: - passenger data - aircraft data - staff data - revenue data ==Databases provide the infratructure (system) to store, organize, and retreive data (big or small) in an efficient way== ### 1.1.2 What is a Database? >[! cue] [[Database]] >[! info] Database >An organized collection of data stored electronically that allows for efficient retrieval and manipulation Why is this important? This is a system that enables a number of complex operations to be performed on the data. - calculating total revenue - tracking inventory - Analysis ### 1.1.3 Database Management System (DBMS) >[! cue] [[Database Management System (DBMS)|DBMS]] >[! info] Database Management System (DBMS) >Is a special kind of software called DBMS that enables users to interact with data stored in a database. - PostgreSQL - MySQL - SQLite are examples of DBMS ### 1.1.4 Types of Databases >[! cue] Types of Databases Two main types of many others. - Relational - Non-relational #### Relational Databases DB's organized into tables. - each table organized into - Rows - Columns >[! cue] [[Record]], [[Attribute]], [[Primary key]] Each row is one record - a row's unique identifier is called a [[Primary key]] - primary keys used to link tables together. Each column is one specific attribute of that record. - example. Each row is a customer, and the columns could be First Name, Last Name, social security number These kinds of databases use [[SQL]] to CRUD data The DBMS systems listed in the last section are predomitatley relational database management systems, AKA RDBMS #### Non-Relational Databases AKA NoSQL databases do not use tables to stare data. The data stored in these databases are stored in other data models like Key Value Pairs, Documents, graphs, or just columnar stors. Redis, Mongo DB, and Cassandra are popular noSQL databases. --- ## 1.2 What is SQL >[! warning] 1.2 What is SQL Takeaways >- SQL is declarative, not imperative >- SQL statements made up of SQL Clauses which contain one or more SQL Keywords that describe what we want to accomplish >- There are many flavors of SQL that might have differing syntax, but achieve the same goals. >[! cue] Def: [[SQL]] >[! info] SQL >Structured query languge. A standardized programming language used to interact with databases. ### 1.2.1 SQL is a Declarative >[! cue] SQL is a Declarative Language > > > > This means that we need to describe what we want to achieve rather than the steps needed in how to achieve what we want. This is in contrast to [[Python]] which is an imperative language. ### 1.2.2 SQL is case insensitive No differentiation at run time between `SELECT * FROM fares` vs `select * from fares` ### 1.2.3 Components of SQL - Relational DB has the following components - [[Table]] a collection of data organized into rows and columns - [[Record]] aka Row - [[Attribute]] aka Column - SQL statements aka queries are the commands that we use to interact with the DB - Statements are made up of [[SQL clause|clauses]]. - Clauses are a keyword or group of keywords and conditions that perform a specific task. Example statement ```postgresql SELECT * FROM students WHERE age >18; ``` The example has the following clauses `SELECT` clause, `FROM` clause, and `WHERE` clause. The `SELECT` clause uses only the `SELECT` keyword, in this example. #### Common Clauses [[SELECT]] [[FROM]] [[WHERE]] [[GROUP BY]] [[ORDER BY]] [[LIMIT]] ### 1.2.4 Flavors of SQL SQL itself is based on a standard. But there are many branches or "flavors" of SQL that are based on the standard but have relatively different implementations. Mostly the differences are in some of the syntax but most of the syntax is portable among flavors. --- ## 1.3 Types of Queries >[! warning] Types of Queries key takeaways >Five types of Queries; three main ones are: DQL, DML, DDL - Different Query Types designed to accomplish different tasks - 5 groups - DQL: Data Query Language ... Retrieve in CRUD - DML: Data Manipulation Language ... Update in CRUD - DDL: Data Definition Language. - DCL: Data Control Language - Intro only - TCL: Transaction Control Language - Intro only - ==This course focus on DQL, DML, DDL== ### 1.3.1 Data Query Language (DQL) >[! cue] Query type: [[Data Query Language (DQL)]] >[! info] DQL >Used to retrieve data from a database. Primary statements used in DQL are `SELECT` statements #### Example DQL Query Simple Select statement. an example of a DQL type ```postgresql SELECT * FROM users; ``` ### 1.3.2 Data Manipulation Language (DML) >[! cue] Query Type: [[Data Manipulation Language (DML)]] >[! info] [[Data Manipulation Language (DML)]] >This query type is used to modify data in a database. Most common statements for this type of query: [[INSERT]] [[UPDATE]] [[DELETE]] #### Examples DML queries See : [[INSERT INTO]]: this adds a new record to existing table ```postgresql INSERT INTO users (name, email, age) VALUES ('John Doe', '[email protected]', 25); ``` Example of updates of existing records in table ```sql UPDATE users SET email = '[email protected]' WHERE id = 1; ``` Example of deleting a record off of a db ```SQL DELETE FROM users WHERE id = 1; ``` ### 1.3.3 Data Definition Language (DDL) >[! cue] Query Type: [[Data Definition Language (DDL)]] >[! info] Data Definition Language (DDL) >DDL Statements set up constraints of a table/database. These statements used to create, modify, and delete ==database objects== like [[Table]], indexes, and views. Common DDL statements: [[CREATE]] see [[CREATE TABLE]] [[ALTER]] and [[DROP]] see [[DROP TABLE]] #### Example of DDL query the create table statement names the table `users`, and then defines the datatypes of each of the attributes within the table such as `id` , the data type, and additional constraint [[Primary key]] ```sql CREATE TABLE users ( id INT PRIMARY KEY, name VARCHAR(255), email VARCHAR(255), age INT ); ``` ### 1.3.4 Data Control Language (DCL) This type of query is used to manage access permissions and security within a database. Most common DCL statements `GRANT` `REVOKE` Example to allow user named `john` to use `SELECT`, `INSERT`, `UPDATE`, `DELETE` privileges on the table named `users` ``` postgresql GRANT SELECT, INSERT, UPDATE, DELETE ON users TO john; ``` ### 1.3.5 Transaction Control Language (TCL) These manage db transactions to ensure data integrity. Most common statements in this query type are `COMMIT` `ROLLBACK` `SAVEPOINT` --- ## 1.4 Next Steps This section contained notes on how to use the coding environment. No notes were taken here because this was more of a housekeeping section. ## 1.5 Exercises and QUIZ - What does SQL stand for? - Which type of DB organizes data into tables, rows, columns? - What statement is wrong about SQL? - This had two wrong answers - SQL is a procedural language (it is declaritive) - SQL is a DBMS (it is a programming language to interact with dbms) <hr> >[!summary-top] 1. Introduction Unit Takeaways >- DB's are a way to organize data while DBMS's are what is used to CRUD the data within a DB >- SQL is used to CRUD data in a relational DBMS >- SQL is declarative and is not a dbms in and of itself. >- SQL keywords build into SQL Clauses which then build into SQL Statements >- There are 5 types of queries. Each type is designed to accomplish specific things. [[Nexus - Master SQL Course#1. Introduction|Back to Course Nexus]]