Download - INFANL01-3 ANALYSE 3 WEEK 3 March 2015 Institute voor Communication, Media en Informatietechnology
![Page 1: INFANL01-3 ANALYSE 3 WEEK 3 March 2015 Institute voor Communication, Media en Informatietechnology](https://reader035.vdocuments.us/reader035/viewer/2022062523/5a4d1b127f8b9ab059990384/html5/thumbnails/1.jpg)
INFANL01-3 ANALYSE 3
WEEK 3
March 2015Institute voor Communication, Media en Informatietechnology
![Page 2: INFANL01-3 ANALYSE 3 WEEK 3 March 2015 Institute voor Communication, Media en Informatietechnology](https://reader035.vdocuments.us/reader035/viewer/2022062523/5a4d1b127f8b9ab059990384/html5/thumbnails/2.jpg)
AGENDA
▸ CREATE ,
▸ INSERT, SELECT en
▸ UPDATE
![Page 3: INFANL01-3 ANALYSE 3 WEEK 3 March 2015 Institute voor Communication, Media en Informatietechnology](https://reader035.vdocuments.us/reader035/viewer/2022062523/5a4d1b127f8b9ab059990384/html5/thumbnails/3.jpg)
CREATE
![Page 4: INFANL01-3 ANALYSE 3 WEEK 3 March 2015 Institute voor Communication, Media en Informatietechnology](https://reader035.vdocuments.us/reader035/viewer/2022062523/5a4d1b127f8b9ab059990384/html5/thumbnails/4.jpg)
CREATE▸ SQL CREATE DATABASE Syntax
▸ The SQL CREATE TABLE Statement▸ The CREATE TABLE statement is used to create a table in a
database.▸ Tables are organized into rows and columns; and each table must
have a name▸ SQL CREATE TABLE Syntax
CREATE DATABASE dbname;
CREATE TABLE table_name(column_name1 data_type(size),column_name2 data_type(size),column_name3 data_type(size),....);
![Page 5: INFANL01-3 ANALYSE 3 WEEK 3 March 2015 Institute voor Communication, Media en Informatietechnology](https://reader035.vdocuments.us/reader035/viewer/2022062523/5a4d1b127f8b9ab059990384/html5/thumbnails/5.jpg)
EXAMPLE
▸ See http://www.w3schools.com/sql/sql_create_db.asp▸ http://www.w3schools.com/sql/sql_create_table.asp
![Page 6: INFANL01-3 ANALYSE 3 WEEK 3 March 2015 Institute voor Communication, Media en Informatietechnology](https://reader035.vdocuments.us/reader035/viewer/2022062523/5a4d1b127f8b9ab059990384/html5/thumbnails/6.jpg)
INSERT
![Page 7: INFANL01-3 ANALYSE 3 WEEK 3 March 2015 Institute voor Communication, Media en Informatietechnology](https://reader035.vdocuments.us/reader035/viewer/2022062523/5a4d1b127f8b9ab059990384/html5/thumbnails/7.jpg)
INSERT
▸ The SQL INSERT INTO Statement▸ The INSERT INTO statement is used to insert new records in a
table
▸ SQL INSERT INTO Syntax▸ It is possible to write the INSERT INTO statement in two forms.▸ The first form does not specify the column names where the data
will be inserted, only their values:
▸ The second form specifies both the column names and the values to be inserted:
INSERT INTO table_nameVALUES (value1,value2,value3,...);
INSERT INTO table_name (column1,column2,column3,...)VALUES (value1,value2,value3,...)
![Page 8: INFANL01-3 ANALYSE 3 WEEK 3 March 2015 Institute voor Communication, Media en Informatietechnology](https://reader035.vdocuments.us/reader035/viewer/2022062523/5a4d1b127f8b9ab059990384/html5/thumbnails/8.jpg)
EXAMPLE
▸ See: http://www.w3schools.com/sql/sql_insert.asp
![Page 9: INFANL01-3 ANALYSE 3 WEEK 3 March 2015 Institute voor Communication, Media en Informatietechnology](https://reader035.vdocuments.us/reader035/viewer/2022062523/5a4d1b127f8b9ab059990384/html5/thumbnails/9.jpg)
SELECT
![Page 10: INFANL01-3 ANALYSE 3 WEEK 3 March 2015 Institute voor Communication, Media en Informatietechnology](https://reader035.vdocuments.us/reader035/viewer/2022062523/5a4d1b127f8b9ab059990384/html5/thumbnails/10.jpg)
SELECT
▸ The SQL SELECT Statement▸ The SELECT statement is used to select data from a database.▸ The result is stored in a result table, called the result-set.
▸ SQL SELECT Syntax▸ It is possible to write the INSERT INTO statement in two forms.
▸ and
SELECT column_name,column_nameFROM table_name;
SELECT * FROM table_name;
![Page 11: INFANL01-3 ANALYSE 3 WEEK 3 March 2015 Institute voor Communication, Media en Informatietechnology](https://reader035.vdocuments.us/reader035/viewer/2022062523/5a4d1b127f8b9ab059990384/html5/thumbnails/11.jpg)
EXAMPLE
▸ See http://www.w3schools.com/sql/sql_select.asp
![Page 12: INFANL01-3 ANALYSE 3 WEEK 3 March 2015 Institute voor Communication, Media en Informatietechnology](https://reader035.vdocuments.us/reader035/viewer/2022062523/5a4d1b127f8b9ab059990384/html5/thumbnails/12.jpg)
UPDATE
![Page 13: INFANL01-3 ANALYSE 3 WEEK 3 March 2015 Institute voor Communication, Media en Informatietechnology](https://reader035.vdocuments.us/reader035/viewer/2022062523/5a4d1b127f8b9ab059990384/html5/thumbnails/13.jpg)
UPDATE
▸ The SQL UPDATE Statement▸ The UPDATE statement is used to update existing records in a
table.
▸ SQL UPDATESyntax
◦ Notice the WHERE clause in the SQL UPDATE statement!◦ The WHERE clause specifies which record or records that
should be updated. If you omit the WHERE clause, all records will be updated!
UPDATE table_nameSET column1=value1,column2=value2,...WHERE some_column=some_value;
![Page 14: INFANL01-3 ANALYSE 3 WEEK 3 March 2015 Institute voor Communication, Media en Informatietechnology](https://reader035.vdocuments.us/reader035/viewer/2022062523/5a4d1b127f8b9ab059990384/html5/thumbnails/14.jpg)
EXAMPLE
▸ See http://www.w3schools.com/sql/sql_select.asp
![Page 15: INFANL01-3 ANALYSE 3 WEEK 3 March 2015 Institute voor Communication, Media en Informatietechnology](https://reader035.vdocuments.us/reader035/viewer/2022062523/5a4d1b127f8b9ab059990384/html5/thumbnails/15.jpg)
JOIN
![Page 16: INFANL01-3 ANALYSE 3 WEEK 3 March 2015 Institute voor Communication, Media en Informatietechnology](https://reader035.vdocuments.us/reader035/viewer/2022062523/5a4d1b127f8b9ab059990384/html5/thumbnails/16.jpg)
JOINS▸ SQL JOIN▸ An SQL JOIN clause is used to combine rows from two or more
tables, based on a common field between them.▸ The most common type of join is: SQL INNER JOIN (simple join). An
SQL INNER JOIN return all rows from multiple tables where the join condition is met
▸ SQL INNER JOIN Syntax
▸ Different SQL JOINs
SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDateFROM OrdersINNER JOIN CustomersON Orders.CustomerID=Customers.CustomerID;
INNER JOIN: Returns all rows when there is at least one match in BOTH tablesLEFT JOIN: Return all rows from the left table, and the matched rows from the right tableRIGHT JOIN: Return all rows from the right table, and the matched rows from the left tableFULL JOIN: Return all rows when there is a match in ONE of the tables
![Page 17: INFANL01-3 ANALYSE 3 WEEK 3 March 2015 Institute voor Communication, Media en Informatietechnology](https://reader035.vdocuments.us/reader035/viewer/2022062523/5a4d1b127f8b9ab059990384/html5/thumbnails/17.jpg)
EXAMPLE
▸ See http://www.w3schools.com/sql/sql_join.asp
![Page 18: INFANL01-3 ANALYSE 3 WEEK 3 March 2015 Institute voor Communication, Media en Informatietechnology](https://reader035.vdocuments.us/reader035/viewer/2022062523/5a4d1b127f8b9ab059990384/html5/thumbnails/18.jpg)