TLDR;
This SQL tutorial provides a comprehensive guide for beginners to learn SQL, covering database basics, installation of MySQL, writing SQL queries, and database schema design. It explains relational database concepts like tables, keys, and SQL basics, including CRUD operations. The course also covers advanced techniques such as designing database schemas, understanding ER diagrams, and using triggers.
- Introduction to SQL and relational databases
- Installation and setup of MySQL and PopSQL
- Writing basic and advanced SQL queries
- Designing database schemas and understanding ER diagrams
- Using triggers for automated database actions
SQL Tutorial - Full Database Course for Beginners [0:00]
The course aims to teach everything needed to start using SQL, covering database basics, types, and SQL's role in creating databases. It includes installing MySQL, writing SQL code for database and table creation, data input/retrieval, and SQL queries for specific information. The course will also cover advanced techniques for data retrieval and database schema design, with all code available on the Giraffe Academy website.
What is a database? [2:36]
This section introduces databases, their uses, and different types. A database is defined as any collection of related information, like a phonebook or Facebook's userbase, stored on paper, in mind, or on a computer. Computers are useful for storing large databases because they offer security, backup, and duplication capabilities. Database management systems (DBMS) are special software programs that help users create and maintain databases, handle security, backup data, and interact with software applications.
Tables and Keys [23:13]
This section covers core concepts in relational databases, including tables, columns, rows, and keys. Tables store data in columns and rows, with each row representing an entry. A primary key uniquely identifies each row, differentiating entries with the same name or major. Foreign keys link tables by storing the primary key of another table, defining relationships between tables. Surrogate keys are random numbers assigned as primary keys, while natural keys have real-world meaning, like a Social Security Number. Composite keys use multiple columns to uniquely identify rows.
SQL Basics [43:34]
This section introduces SQL (Structured Query Language), a language for interacting with relational database management systems (RDBMS). SQL is used to create, retrieve, update, and delete data, manage databases, design tables, and perform administrative tasks. SQL implementations vary between systems like Postgres and MySQL, but the core concepts remain the same. SQL is a hybrid language comprising data query, definition, control, and manipulation languages. Queries are instructions to the RDBMS for retrieving specific information from the database.
MySQL Windows Installation [52:29]
This section guides through downloading and installing MySQL Community Server, a free RDBMS, and PopSQL, a text editor for writing SQL queries. The installation process involves selecting a custom setup, choosing MySQL Server and MySQL Shell, setting a root password, and configuring the MySQL server. After installation, a database named "giraffe" is created using the MySQL command-line client. PopSQL is then downloaded and connected to the MySQL database for writing and executing SQL queries.
MySQL Mac Installation [1:02:02]
This section provides a tutorial on downloading and installing MySQL and PopSQL on a Mac. It includes instructions for downloading the MySQL Community Server, installing it, and noting the temporary password. The tutorial also covers starting the MySQL server, updating the password via the terminal, and creating a database named "giraffe." Finally, it guides through downloading and connecting PopSQL to the MySQL database for writing SQL queries.
Creating Tables [1:15:52]
This section covers datatypes, creating, altering, and deleting tables in SQL. Common datatypes include INT, DECIMAL, VARCHAR, BLOB, DATE, and TIMESTAMP. Tables are created using the CREATE TABLE statement, defining column names and datatypes. Primary keys are specified to uniquely identify rows. Tables can be altered to add or drop columns and deleted using the DROP TABLE statement.
Inserting Data [1:31:09]
This section explains how to insert data into database tables using the INSERT INTO statement. Data is inserted by specifying the table name and values for each column. Specific columns can be targeted for insertion, allowing other columns to default to NULL. Duplicate entries with the same primary key are not allowed.
C [1:38:20]
This section discusses advanced techniques for inserting data into database tables, including the use of NOT NULL, UNIQUE, DEFAULT, and AUTO_INCREMENT constraints. NOT NULL ensures that a column cannot have a NULL value, while UNIQUE ensures that all values in a column are unique. DEFAULT sets a default value for a column if no value is specified during insertion. AUTO_INCREMENT automatically increments the value of a column, typically used for primary keys.
Update & Delete [1:48:14]
This section covers updating and deleting rows in a database table. The UPDATE statement modifies existing data, while the DELETE FROM statement removes rows. Both statements can be used with a WHERE clause to specify conditions for updating or deleting specific rows. Without a WHERE clause, the UPDATE statement will modify all rows in the table, and the DELETE FROM statement will remove all rows.
Basic Queries [1:56:14]
This section introduces basic SQL queries using the SELECT keyword to retrieve information from a database. It covers selecting all columns or specific columns, ordering results using ORDER BY, limiting the number of results using LIMIT, and filtering results using WHERE with comparison operators like =, <, >, <=, >=, !=, AND, OR, and IN.
Company Database Into [2:08:40]
This section introduces a more complex database schema for a company, including tables for employees, branches, clients, branch suppliers, and work relationships. It explains the relationships between these tables, including foreign keys and composite keys. The database schema is designed to facilitate more advanced SQL queries.
Creating Company Database [2:17:08]
This section provides a step-by-step guide to creating a complex database schema in MySQL, including creating tables for employees, branches, clients, branch suppliers, and work relationships. It covers defining primary keys, foreign keys, and composite keys, as well as inserting data into the tables while managing foreign key relationships. The tutorial also addresses the use of ON DELETE SET NULL and ON DELETE CASCADE.
More Basic Queries [2:30:30]
This section demonstrates various SELECT statements to query the company database. It covers retrieving all employees, ordering employees by salary, ordering employees by sex and name, limiting the number of results, selecting specific columns, renaming columns using AS, and finding distinct values using DISTINCT.
Functions [2:36:28]
This section introduces SQL functions for performing calculations and aggregations on data. It covers functions like COUNT, AVG, and SUM, as well as the GROUP BY clause for aggregating data. Examples include finding the number of employees, the average salary, and the total sales for each salesman.
Wildcards [2:45:16]
This section explains how to use wildcards and the LIKE keyword in SQL to match specific patterns in data. It covers the use of the percent sign (%) to represent any number of characters and the underscore (_) to represent a single character. Examples include finding clients who are LLCs, branch suppliers in the label business, and employees born in October.
Union [2:53:55]
This section covers the UNION operator in SQL, which combines the results of multiple SELECT statements into one. It explains the rules for using UNION, such as having the same number of columns and similar datatypes in each SELECT statement. Examples include finding a list of employee and branch names and a list of all money spent or earned by the company.
Joins [3:01:39]
This section introduces JOINs in SQL, which are used to combine rows from two or more tables based on a related column. It covers inner JOINs, LEFT JOINs, and RIGHT JOINs, explaining how each type of JOIN includes rows from the tables. The tutorial also discusses the concept of a FULL OUTER JOIN, which is not supported in MySQL.
Nested Queries [3:11:53]
This section explains nested queries in SQL, where multiple SELECT statements are used to get specific information. It demonstrates how to use the results of one SELECT statement to inform the results of another SELECT statement. Examples include finding the names of all employees who have sold over $30,000 to a single client and finding all clients who are handled by the branch that Michael Scott manages.
On Delete [3:21:56]
This section discusses deleting entries in a database when they have foreign keys associated with them. It covers the use of ON DELETE SET NULL and ON DELETE CASCADE to handle this situation. ON DELETE SET NULL sets the foreign key to NULL when the referenced row is deleted, while ON DELETE CASCADE deletes the entire row with the foreign key.
Triggers [3:30:08]
This section explains how to use triggers in SQL and MySQL. Triggers are blocks of SQL code that define certain actions that should happen when a certain operation gets performed on the database. The tutorial covers creating triggers for INSERT, UPDATE, and DELETE operations, as well as using conditionals (IF, ELSEIF, ELSE) within triggers.
ER Diagrams Intro [3:42:16]
This section provides an introduction to ER diagrams, which are used to design database schemas. It covers the basic parts of an ER diagram, including entities, attributes, primary keys, composite attributes, multi-valued attributes, derived attributes, relationships, participation, and relationship cardinality. The tutorial also introduces weak entity types and identifying relationships.
Designing an ER Diagram [3:55:57]
This section walks through creating an ER diagram based on company data requirements. It covers identifying entities, attributes, relationships, participation constraints, and cardinality ratios from a requirements document. The tutorial demonstrates how to map these elements into an ER diagram.
Converting ER Diagrams to Schemas [4:08:37]
This section explains how to convert an ER diagram into a database schema. It covers mapping regular entity types, weak entity types, binary 1 to 1 relationship types, binary 1 to N relationship types, and binary M to N relationship types into database tables and relations. The tutorial also demonstrates how to define foreign keys and composite keys based on the relationships in the ER diagram.