SQL database

SQL involving related tables

SQL statements

• - used to ask questions in the database
• -method of asking questions, way of retrieving certain kind of data
• -sql stands for structured query language
• -it is a programming language that allows you to create query and manipulate database

the select statement

• SQL statement will select all the members and put all the columns in a result table
• you can use a wild card to mean all columns
• you can select the columns that you want in the result table
• select first name user name
• from tbimember
• <- picture not related
• 
• Bildunterschrift: example of salect

the where clause

• - the where clause is used to extract only records satisfying a specified criterion.
• you have already used some of these operators in queries:
• =, <>, >, <, >=, <=, AND, OR ,NOT
• you can use these operators in SQL queries
• select
• from tbimember
• where town = "Colchester" OR town "Ipswich"
• EXAMPLE of where and select
• select first name, surname
• from tbimember
• where (town = "Colchester" OR town "Ipswich") AND gender = "f"

sorting: the order by keyword

• -the order by sorts the query results in ascending order by default
• -u can sort in sdescending order by adding "DESC"
• select *
• from tbimember
• order by surname, first name
• select *
• from tbimember
• order by surname, first name DESC

inserting records

• the insert into statement inserts records in a table
• if you are inserting data into every column, you do not need to specify the column names
• INSERT INTO tblmember
• VALUES (6, 'martin', 'hodgins', 'M', 'colchester')
• to insert data into some columns, leaving others blank, you must specify the column names:
• INSERT INTO tblmember (member id, first name, surname)
• VALUES (7, 'LES', 'williams', 'ipswich')

updating records

• you can update the records of one or more records
• UPDATE tblmember
• SET town = "hadleigh"
• WHERE member id = 2
• (in table number two change the town into hadleigh)

SQL involving related tables

• SELECT tbimember, first name, tbimember, surname, tblsessionday, tblsession time
• FROM tblmember
• INNER JOIN tblsession ON tbi session id = tbisession, peiod id