sql john nowobilski. what is sql? structured query language manages data in database management...
TRANSCRIPT
![Page 1: SQL John Nowobilski. What is SQL? Structured Query Language Manages Data in Database Management Systems based on the Relational Model Developed in 1970s](https://reader035.vdocuments.us/reader035/viewer/2022062322/56649f465503460f94c67a6f/html5/thumbnails/1.jpg)
SQLJohn Nowobilski
![Page 2: SQL John Nowobilski. What is SQL? Structured Query Language Manages Data in Database Management Systems based on the Relational Model Developed in 1970s](https://reader035.vdocuments.us/reader035/viewer/2022062322/56649f465503460f94c67a6f/html5/thumbnails/2.jpg)
What is SQL?Structured Query Language
Manages Data in Database Management Systems based on the Relational Model
Developed in 1970s by IBM
![Page 3: SQL John Nowobilski. What is SQL? Structured Query Language Manages Data in Database Management Systems based on the Relational Model Developed in 1970s](https://reader035.vdocuments.us/reader035/viewer/2022062322/56649f465503460f94c67a6f/html5/thumbnails/3.jpg)
Relational DatabaseMatches data by common
characteristics within data set
Based on relational algebra and relational calculus
Also known as a schema
![Page 4: SQL John Nowobilski. What is SQL? Structured Query Language Manages Data in Database Management Systems based on the Relational Model Developed in 1970s](https://reader035.vdocuments.us/reader035/viewer/2022062322/56649f465503460f94c67a6f/html5/thumbnails/4.jpg)
RDBMSRelational Database Management
System
DBMS where data and relationships among data are stored in tables
Data can be accessed without changing forms of tables
![Page 5: SQL John Nowobilski. What is SQL? Structured Query Language Manages Data in Database Management Systems based on the Relational Model Developed in 1970s](https://reader035.vdocuments.us/reader035/viewer/2022062322/56649f465503460f94c67a6f/html5/thumbnails/5.jpg)
Advanced Clothing Solutions
![Page 6: SQL John Nowobilski. What is SQL? Structured Query Language Manages Data in Database Management Systems based on the Relational Model Developed in 1970s](https://reader035.vdocuments.us/reader035/viewer/2022062322/56649f465503460f94c67a6f/html5/thumbnails/6.jpg)
QueriesWay to access information from the
Database
All data and relationships are stored in tables
SQL: “SELECT - FROM - WHERE” statement format to access the relations/data stored in schema
![Page 7: SQL John Nowobilski. What is SQL? Structured Query Language Manages Data in Database Management Systems based on the Relational Model Developed in 1970s](https://reader035.vdocuments.us/reader035/viewer/2022062322/56649f465503460f94c67a6f/html5/thumbnails/7.jpg)
TablesName of the table is the object you
are trying to represent
Consist of a tuple of columns that contain information about that table
OOP: member variables ≈ columns
![Page 8: SQL John Nowobilski. What is SQL? Structured Query Language Manages Data in Database Management Systems based on the Relational Model Developed in 1970s](https://reader035.vdocuments.us/reader035/viewer/2022062322/56649f465503460f94c67a6f/html5/thumbnails/8.jpg)
Column DatatypesEach column has associated datatype
Set of “primitive” types as in OOP
INT, CHAR, VARCHAR, BOOL, FLOAT
DATE, TIME, YEAR, BLOB
![Page 9: SQL John Nowobilski. What is SQL? Structured Query Language Manages Data in Database Management Systems based on the Relational Model Developed in 1970s](https://reader035.vdocuments.us/reader035/viewer/2022062322/56649f465503460f94c67a6f/html5/thumbnails/9.jpg)
SELECT - FROM“SELECT” clause accesses columns from
a selected table
Access all columns, or specify which you want to access
“FROM” clause determines which tables to access columns from
![Page 10: SQL John Nowobilski. What is SQL? Structured Query Language Manages Data in Database Management Systems based on the Relational Model Developed in 1970s](https://reader035.vdocuments.us/reader035/viewer/2022062322/56649f465503460f94c67a6f/html5/thumbnails/10.jpg)
SELECT * FROM articles;
SELECT stock, rating, price FROM inventory;
![Page 11: SQL John Nowobilski. What is SQL? Structured Query Language Manages Data in Database Management Systems based on the Relational Model Developed in 1970s](https://reader035.vdocuments.us/reader035/viewer/2022062322/56649f465503460f94c67a6f/html5/thumbnails/11.jpg)
WHERE clauseAdds constraints to returned results from
queries
Allows for relationships to be accessed between multiple tables
“AND” clause adds more constraints to returned results
![Page 12: SQL John Nowobilski. What is SQL? Structured Query Language Manages Data in Database Management Systems based on the Relational Model Developed in 1970s](https://reader035.vdocuments.us/reader035/viewer/2022062322/56649f465503460f94c67a6f/html5/thumbnails/12.jpg)
SELECT * FROM articlesWHERE size =‘M’ AND gender =‘F’;
SELECT price, type FROM articles, inventoryWHERE articles.id = inventory.articleID;
![Page 13: SQL John Nowobilski. What is SQL? Structured Query Language Manages Data in Database Management Systems based on the Relational Model Developed in 1970s](https://reader035.vdocuments.us/reader035/viewer/2022062322/56649f465503460f94c67a6f/html5/thumbnails/13.jpg)
DISTINCT clauseAllows for distinct results from
columns to be returned
Avoids redundancy in results
![Page 14: SQL John Nowobilski. What is SQL? Structured Query Language Manages Data in Database Management Systems based on the Relational Model Developed in 1970s](https://reader035.vdocuments.us/reader035/viewer/2022062322/56649f465503460f94c67a6f/html5/thumbnails/14.jpg)
SELECT DISTINCT descriptionFROM articlesWHERE gender = ‘M’ AND type = ‘shirt’;
![Page 15: SQL John Nowobilski. What is SQL? Structured Query Language Manages Data in Database Management Systems based on the Relational Model Developed in 1970s](https://reader035.vdocuments.us/reader035/viewer/2022062322/56649f465503460f94c67a6f/html5/thumbnails/15.jpg)
CREATE TABLECreates table in the database
Specify column names in CREATE TABLE statement
NOT NULL ensures column isn’t null when entries are inserted
![Page 16: SQL John Nowobilski. What is SQL? Structured Query Language Manages Data in Database Management Systems based on the Relational Model Developed in 1970s](https://reader035.vdocuments.us/reader035/viewer/2022062322/56649f465503460f94c67a6f/html5/thumbnails/16.jpg)
CREATE TABLE articles(‘id’ INT NOT NULL,‘company_id’ INT NOT NULL,‘type’ VARCHAR(45),‘description’ VARCHAR(160),‘size’ VARCHAR(5),‘color’ VARCHAR(30),‘gender’ VARCHAR(1));
![Page 17: SQL John Nowobilski. What is SQL? Structured Query Language Manages Data in Database Management Systems based on the Relational Model Developed in 1970s](https://reader035.vdocuments.us/reader035/viewer/2022062322/56649f465503460f94c67a6f/html5/thumbnails/17.jpg)
INSERTAdds entry into a table in the
database
Can populate as many or as few columns as you want, as long as they’re not required for insertion
![Page 18: SQL John Nowobilski. What is SQL? Structured Query Language Manages Data in Database Management Systems based on the Relational Model Developed in 1970s](https://reader035.vdocuments.us/reader035/viewer/2022062322/56649f465503460f94c67a6f/html5/thumbnails/18.jpg)
INSERT INTOarticles(gender, type, color)VALUES(‘M’, ‘shirt’, ‘red’);
![Page 19: SQL John Nowobilski. What is SQL? Structured Query Language Manages Data in Database Management Systems based on the Relational Model Developed in 1970s](https://reader035.vdocuments.us/reader035/viewer/2022062322/56649f465503460f94c67a6f/html5/thumbnails/19.jpg)
UPDATEUpdates column values in a table
Can update certain entries, or all entries in the table at once
![Page 20: SQL John Nowobilski. What is SQL? Structured Query Language Manages Data in Database Management Systems based on the Relational Model Developed in 1970s](https://reader035.vdocuments.us/reader035/viewer/2022062322/56649f465503460f94c67a6f/html5/thumbnails/20.jpg)
UPDATE articles SET size=‘S’, color=‘blue’WHEREType=‘shirt’;
![Page 21: SQL John Nowobilski. What is SQL? Structured Query Language Manages Data in Database Management Systems based on the Relational Model Developed in 1970s](https://reader035.vdocuments.us/reader035/viewer/2022062322/56649f465503460f94c67a6f/html5/thumbnails/21.jpg)
DELETERemoves entries from a table
Similar to “SELECT-FROM-WHERE” format
Use “DROP” to delete tables
![Page 22: SQL John Nowobilski. What is SQL? Structured Query Language Manages Data in Database Management Systems based on the Relational Model Developed in 1970s](https://reader035.vdocuments.us/reader035/viewer/2022062322/56649f465503460f94c67a6f/html5/thumbnails/22.jpg)
DELETE FROM articlesWHERE size=‘S’ AND color=‘blue’;
DROP TABLE articles;
![Page 23: SQL John Nowobilski. What is SQL? Structured Query Language Manages Data in Database Management Systems based on the Relational Model Developed in 1970s](https://reader035.vdocuments.us/reader035/viewer/2022062322/56649f465503460f94c67a6f/html5/thumbnails/23.jpg)
Thank youSources:
http://en.wikipedia.org/wiki/SQL
http://en.wikipedia.org/wiki/Relational_database_management_system
http://www.w3schools.com/sql/sql_syntax.asp