- OPINION: El lenguaje SQL: por qué es uno de los conocimientos técnicos más valiosos que puedes adquirir
SQL, Structured Query Language, is a programming language designed to manage data stored in relational databases. SQL operates through simple, declarative statements. This keeps data accurate and secure, and it helps maintain the integrity of databases, regardless of size.
Here’s an appendix of commonly used commands.
Learn SQL by popSQL.com
Learn SQL in 4 hours by freeCodeCamp.org
(2:36) What is a Database?
(23:10) Tables & Keys
(43:31) SQL Basics
(52:26) MySQL Windows Installation
(1:01:59) MySQL Mac Installation
(1:15:49) Creating Tables
(1:31:05) Inserting Data
(1:48:11) Update & Delete
(1:56:11) Basic Queries
(2:08:37) Company Database Intro
(2:14:05) Creating Company Database
(2:30:27 ) More Basic Queries
(3:11:49) Nested Queries
(3:21:52) On Delete
(3:42 12) FR Diagrams Intro
(3:55:53) Designing an ER Diagram
(4:08:34) Converting ER Diagrams to Schemas
SQL DATA TYPES
INT -- Whole Numbers
DECIMAL (M,N) -- Decimal Numbers - Exact Value M=total number of digits N=number of digits after , -> e.g. M=4, N=2 is 34,56
VARCHAR (1) -- String of text of length 1
BLOB -- Binary Large Object, Stores large data
DATE -- 'YYYY-MM-DD'
TIMESTAMP -- 'YYYY-MM-DD HH:MM:SS' - used for recording date and time
ENTITY/RELATIONSHIP DIAGRAMS (E/R)
Entity: An object we want to model & store information about (symbol= a rectangle)
Multiple Entities: You can define more than one entity in the diagram
Weak Entity: An entity that cannot be uniquely identified by its attributes alone (symbol: double line rectangle)
Attributes: Specific pieces of information about an entity (symbol= an oval connected to the entity)
Primary Key: An attribute(s) that uniquely identify an entry in the database table (an oval with underligned text)
Composite Attribute: An attribute that can be broken up into sub-attributes (e.g. Name = Forename and Lastname)
Multi-valued Attribute: An attribute that can have more than one value (symbol: double line diamond)
Derived Attribute: An attribute than can be derived from the other attributes (symbol: dotted line oval, e.g. age cab be derived from birthdate)
Relationship: defines a relationship between two entities (symbol: single line diamond connected to both entities)
Partial participation: Not all members need to participate in the relationship (symbol: single line, e.g. student -> class)
Total participation: All members must participate in the relationship (symbol: double line, e.g. class -> student. There is no class without students)
Relationship Cardinality: The number of instances of an entity from a relation that can be associated with the relation (1:1, 1:N, N:M. e.g. N students TAKE M classes)
Identifying relationship; A relationship that serves to uniquely identify the weak entity (symbol: double line diamond)
Step 1: Mapping of Regular Entity Types
For each regular entity type create a relation (table) that includes all the simple attributes of that entity.
Step 2: Mapping of Weak Entity Types
For each weak entity type create a relation (table) that includes all the simple attributes of the weak entity.
The primary key of the new relation should be the partial key of the weak entity plus the primary key of its owner.
Step 3: Mapping of Binary 1:1 Relationship Types
Include one side of the relationship as a foreign key in the other. Favor total participation, i.e. include the foreign key on the side which has double line.
Step 4: Mapping of Binary 1:N Relationship Types
Include the one side's primary key as a foreign key on the N side relation (table)
Step 5: Mapping of Binary M:N Relationship Types
Create a new relation (table) who's primary key is a combination of both entites' primary keys. Also include any relationship attributes.
Primary keys and foreign keys
Database design course - Learn how to plan and design a database for beginners (8 hours).