Lecture 24-SQL:The Sequel
Lecture 24 - SQL: The Sequel
Review of Tables and Join
Review of Tables and Join
Table A table stores data.It consists of... a fixed number of columns. data entries stored in rows. To make a table in SQL,use a CREATE TABLE statement: CREATE TABLE[name]AS··.; To create rows of data,UNION together SELECT statements: SELECT [expr]AS [name],[expr]AS [name],.. UNION SELECT [expr]AS [name],[expr]AS [name],... UNION SELECT [expr]AS [name],[expr]AS [name],... To create rows of data from existing tables,use a SELECT statement with a FROM clause: SELECT [columns]FROM [table]WHERE [condition] ORDER BY [order][ASC/DESC]LIMIT [number];
Table A table stores data. It consists of... ● a fixed number of columns. ● data entries stored in rows. SELECT [expr] AS [name], [expr] AS [name], ... UNION SELECT [expr] AS [name], [expr] AS [name], ... UNION SELECT [expr] AS [name], [expr] AS [name], ...; To create rows of data from existing tables, use a SELECT statement with a FROM clause: CREATE TABLE [name] AS ...; To create rows of data, UNION together SELECT statements: SELECT [columns] FROM [table] WHERE [condition] ORDER BY [order] [ASC/DESC] LIMIT [number]; To make a table in SQL, use a CREATE TABLE statement:
Join Demo Given multiple tables,we can join them together by specifying their names, separated by commas,in the FROM clause of a SELECT statement. SELECT FROM table1,table2; When we join two tables,we get a new table with one row for each combination of rows from the original tables. parent child name fur name fur abraham barack abraham long parent child abraham long abraham barack barack short abraham barack barack short abraham barack clinton long abraham clinton clinton long abraham clinton abraham long abraham clinton barack short abraham clinton clinton long
Join Given multiple tables, we can join them together by specifying their names, separated by commas, in the FROM clause of a SELECT statement. SELECT * FROM table1, table2; When we join two tables, we get a new table with one row for each combination of rows from the original tables. parent child abraham barack abraham clinton name fur abraham long barack short clinton long parent child name fur abraham barack abraham long abraham barack barack short abraham barack clinton long abraham clinton abraham long abraham clinton barack short abraham clinton clinton long Demo
Check Your Understanding Table songs: Table albums: name artist album name artist release_year Table artists: name first_year_active 1. Write an SQL query that outputs the first 10 artists who became active after 2015. SELECT name FROM artists WHERE first_year_active 2015 LIMIT 10; 1.Write an SQL query that outputs the names and artists of songs that were released in 2010 ordered by the first year active of the artist. SELECT s.name,s.artist FROM songs AS s,artists AS ar,albums AS al WHERE album al.name AND s.artist ar.name AND release_year 2010 ORDER BY first_year_active;
Check Your Understanding 1. Write an SQL query that outputs the first 10 artists who became active after 2015. 1. Write an SQL query that outputs the names and artists of songs that were released in 2010 ordered by the first year active of the artist. Table songs: name | artist | album Table artists: name | first_year_active Table albums: name | artist | release_year SELECT name FROM artists WHERE first_year_active > 2015 LIMIT 10; SELECT s.name, s.artist FROM songs AS s, artists AS ar, albums AS al WHERE album = al.name AND s.artist = ar.name AND release_year = 2010 ORDER BY first_year_active;