Home

Temario Lenguaje SQL

Objetivo:

Que el estudiante entienda y aplique completamente el lenguaje de consultas de bases de datos relacionales SQL.

Temas:

  • Data and tables
    • Defining your data
    • Look at your data in categories
    • Databases contain connected data
    • Take command!
    • Setting the table: the CREATE TABLE statement
    • Take a meeting with some data types
    • Your table, DESCribed
    • You can't recreate an existing table or database!
    • Out with the old table, in with the new
    • To add data to your table, you'll use the INSERT statement
    • Create the INSERT statement
    • Variations on an INSERT statement
    • Columns without values
    • Peek at your table with the SELECT statement
    • Controlling your inner NULL
    • NOT NULL appears in DESC
    • Fill in the blanks with DEFAULT
    • Your SQL Toolbox
  • The SELECT statement
    • Date or no date?
    • A better SELECT
    • What the * is that?
    • How to query your data types
    • More punctuation problems
    • Unmatched single quotes
    • Single quotes are special characters
    • INSERT data with single quotes in it
    • SELECT specific columns to limit results
    • SELECT specific columns for faster results
    • Combining your queries
    • Finding numeric values
    • Smooth Comparison Operators
    • Finding numeric data with Comparison Operators
    • Text data roping with Comparison Operators
    • To be OR not to be
    • The difference between AND and OR
    • Use IS NULL to find NULLs
    • Saving time with a single keyword: LIKE
    • The call of the Wild(card)
    • Selecting ranges using AND and comparison operators
    • Just BETWEEN us... there's a better way
    • After the dates, you are either IN...
    • ...or you are NOT IN
    • More NOT
    • Your SQL Toolbox
  • DELETE and UPDATE
    • Clowns are scary
    • Clown tracking
    • How our clown data gets entered
    • Bonzo, we've got a problem
    • Getting rid of a record with DELETE
    • Using our new DELETE statement
    • DELETE rules
    • The INSERTDELETE two step.
    • Be careful with your DELETE
    • The trouble with imprecise DELETE
    • Change your data with UPDATE
    • UPDATE rules
    • UPDATE is the new INSERTDELETE
    • UPDATE in action
    • UPDATE your prices
    • All we need is one UPDATE
    • Your SQL Toolbox
  • Why be normal?
    • Two fishy tables
    • A table is all about relationships
    • Atomic data
    • Atomic data and your tables
    • Reasons to be normal
    • The benefits of normal tables
    • Clowns aren't normal
    • Halfway to 1NF
    • PRIMARY KEY rules
    • Getting to NORMAL
    • Fixing Greg's table
    • The CREATE TABLE we wrote
    • Show me the table
    • Timesaving command
    • The CREATE TABLE with a PRIMARY KEY
    • ... auto incrementally
    • Adding a PRIMARY KEY to an existing table
    • ALTER TABLE and add a PRIMARY KEY
    • Your SQL Toolbox
  • ALTER
    • We need to make some changes
    • Table altering
    • Extreme table makeover
    • Renaming the table
    • We need to make some plans
    • Retooling our columns
    • Structural changes
    • ALTER and CHANGE
    • Change two columns with one SQL statement
    • Quick! DROP that column
    • A closer look at the nonatomic location column
    • Look for patterns
    • A few handy string functions
    • Use a current column to fill a new column
    • How our UPDATE and SET combo works
    • Your SQL Toolbox
  • Advanced SELECT
    • Dataville Video is reorganizing
    • Problems with our current table
    • Matching up existing data
    • Populating the new column
    • S UPDATE with a CASE expression
    • Looks like we have a problem
    • Tables can get messy
    • We need a way to organize the data we SELECT
    • Try a little ORDER BY
    • ORDER a single column
    • ORDER with two columns
    • ORDER with multiple columns
    • An orderly movie_table
    • Reverse the ORDER with DESC
    • The Girl Sprout® cookie sales leader problem
    • SUM can add them for us
    • SUM all of them at once with GROUP BY
    • AVG with GROUP BY
    • MIN and MAX
    • COUNT the days
    • SELECT DISTINCT values
    • LIMIT the number of results
    • LIMIT to just second place
    • Your SQL Toolbox
  • Multitable database design
    • Finding Nigel a date
    • All is lost...
    • But wait
    • Think outside of the single table
    • The multitable clown tracking database
    • The clown_tracking database schema
    • How to go from one table to two
    • Connecting your tables
    • Constraining your foreign key
    • Why bother with foreign keys?
    • CREATE a table with a FOREIGN KEY
    • Relationships between tables
    • Patterns of data: onetoone
    • Patterns of data: when to use onetoone tables
    • Patterns of data: onetomany
    • Patterns of data: getting to manytomany
    • Patterns of data: we need a junction table
    • Patterns of data: manytomany
    • Finally in 1NF
    • Composite keys use multiple columns
    • Shorthand notations
    • Partial functional dependency
    • Transitive functional dependency
    • Second normal form
    • Third normal form (at last)
    • And so, Regis (and gregs_list) lived happily ever after
    • Your SQL Toolbox
    • Joins and multitable operations"Chapter 8. joins and multitable operations
    • Still repeating ourselves, still repeating...
    • Prepopulate your tables
    • We got the "table ain't easy to normalize" blues
    • The special interests (column)
    • Keeping interested
    • UPDATE all your interests
    • Getting all the interests
    • Many paths to one place
    • CREATE, SELECT and INSERT at (nearly) the same time
    • CREATE, SELECT and INSERT at the same time
    • What's up with that AS?
    • Column aliases
    • Table aliases, who needs em?
    • Everything you wanted to know about inner joins
    • Cartesian join
    • Releasing your inner join
    • The inner join in action: the equijoin
    • The inner join in action: the nonequijoin
    • The last inner join: the natural join
    • Joinedup queries? Table and Column Aliases Exposed
    • Your SQL Toolbox
  • Subqueries
    • Greg gets into the job recruiting business
    • Greg's list gets more tables
    • Greg uses an inner join
    • But he wants to try some other queries
    • Subqueries
    • We combine the two into a query with a subquery
    • A subquery construction walkthrough
    • A subquery as a SELECT column
    • Another example: Subquery with a natural join
    • A noncorrelated subquery
    • A noncorrelated subquery with multiple values: IN, NOT IN
    • Correlated subqueries
    • A (useful) correlated subquery with NOT EXISTS
    • EXISTS and NOT EXISTS
    • Greg's Recruiting Service is open for business
    • On the way to the party
    • Your SQL Toolbox
  • Outer joins, selfjoins, and unions
    • Cleaning up old data
    • It's about left and right
    • Here's a left outer join
    • Outer joins and multiple matches
    • The right outer join
    • While you were outer joining...
    • We could create a new table
    • How the new table fits in
    • A selfreferencing foreign key
    • Join the same table to itself
    • We need a selfjoin
    • Another way to get multitable information
    • You can use a UNION
    • UNION is limited
    • UNION rules in action
    • UNION ALL
    • Create a table from your union
    • INTERSECT and EXCEPT
    • We're done with joins, time to move on to...
    • Turning a subquery into a join
    • A selfjoin as a subquery
    • Greg's company is growing
    • Your SQL Toolbox
    • Constraints, views, and transactions
    • Greg's hired some help
    • Jim's first day: Inserting a new client
    • Jim avoids a NULL
    • Flash forward three months
    • CHECK, please: Adding a CHECK CONSTRAINT
    • CHECKing the gender
    • Frank's job gets tedious
    • Creating a view
    • Viewing your views
    • What your view is actually doing
    • What a view is
    • Inserting, updating, and deleting with views
    • The secret is to pretend a view is a real table
    • View with CHECK OPTION
    • Your view may be updatable if...
    • When you're finished with your view
    • When bad things happen to good databases
    • What happened inside the ATM
    • More trouble at the ATM
    • It's not a dream, it's a transaction
    • The classic ACID test
    • SQL helps you manage your transactions
    • What should have happened inside the ATM
    • How to make transactions work with MySQL
    • Now try it yourself
    • Your SQL Toolbox
  • Security
    • User problems
    • Avoiding errors in the clown tracking database
    • Protect the root user account
    • Add a new user
    • Decide exactly what the user needs
    • A simple GRANT statement
    • GRANT variations
    • REVOKE privileges
    • REVOKING a used GRANT OPTION
    • REVOKING with precision
    • The problem with shared accounts
    • Using your role
    • Role dropping
    • Using your role WITH ADMIN OPTION
    • Combining CREATE USER and GRANT
    • Greg's List has gone global!
    • Your SQL Toolbox
    • How about a Greg's List in your city?
    • Use SQL on your own projects, and you too could be like Greg!
    pdf Ver en formato PDF info Ver información del curso