Hello. Last week we learned how to create databases and tables. During this session, we will learn how to insert, delete, and query data through the data manipulation language. There are two sentences to insert and delete data from a relational database. For example, if we need to insert Smith data to the students tables we recreated on our last session, the following code should be executed. As you can see, there is insert into students values. Now, if we need to delete Smith data from the same table, the following code should be executed, delete from students where S name equals Smith. For example, the next SQL code inserts data to the sailors, boats, and reserves tables. Suppose that you don't need a data store on the sailors table, you can remove all the rows from the table. We did truncate sentence without having to drop it. Now, suppose I do insert the data into a table but you realized that data is wrong. Well, there is an update sentence to change the reservation date of a boat. In order to identify which rows you're going to update, you're able to select the data with WHERE and JOIN sentences. Queries can be written intuitively. DBMS is responsible for efficient evaluation. Within this session, we will use the sailors database you created in the past week exercise. Remember the instances of sailors, boats, and reserves relations in our examples. Each select statement must have at least one SELECT and one FROM clause. The other four clauses: WHERE, GROUP BY, HAVING, and ORDER BY can be optional. The order of the clauses is not random but they have a specific order. For example, a GROUP BY clause can never go before the WHERE or FROM clause. An ORDER BY clause is always the last clause. A HAVING clause can be used only if another GROUP BY clause is present as well. From the image of the select statement syntax, we can see that the SELECT clause expects a list of five attributes we want to obtain from the table list. The FROM clause expects a list of table names, those we want to access, possibly with a range variable after each name. The WHERE clause expects a condition to evaluate, to choose rows from the database. Such condition is formed by Attribute1 operator, Attribute2, where operator is one off combined using AND, OR, and NOT. DISTINCT is an optional keyword indicating that the answer should not contain duplicates. Default is that duplicates are not eliminated. If we want to obtain the name and age of all sailors, we should call the following query, SELECT sname, age FROM sailors, or SELECT S.sname, S.age FROM sailors S. Suppose you need to find names of sailors who've reserved boat 103, the name field comes from sailors but the condition is set on the reserves table. This query can be resolved by a subquery and also by a join. A subquery is a SQL query within a query. Subqueries are nested queries that provide data to the enclosing query. Subqueries can return individual values or a list of records. So, queries must be enclosed with parenthesis. It will require to obtain information from two or more tables under recent attributes in common such as primary key and foreign key. The SQL operator required is join. The basic conceptual evaluation strategy of a join consists of the following steps. First, compute a cross-product of relation-list. Second, discard resulting tuples if they fail qualifications by the common column, primary key, foreign key. Third, do not show the attributes that are not in target-list. This strategy is probably the least efficient way to compute a query. An optimizer will find more efficient strategies to compute the same answers. Consider the following information on reserves and sailor tables. If we required to obtain the name of the sailors that have reserved the boat 103, the JOIN query should be like this, SELECT, FROM, WHERE. Now we will see how it was executed by the database manager. First, compute the cross-product of relation-list. Second, discard resulting tuples if they fail qualifications. Third, delete attributes that are not in target-list. Fourth, if DISTINCT is specified, eliminate duplicate rows. If we need to order data, for example, find the names of sailors who have reserved a red boat and list them in the order of age, we will have SELECT, FROM, WHERE, AND, AND, ORDER BY. In the case of expressions and strings, consider the following query, SELECT, FROM, WHERE, LIKE. This query illustrates use of arithmetic expressions and string pattern matching. Find the rating of sailors whose names begin and end with B, and contain at least three characters. AS an equal are two ways to name fields in result. LIKE is used for string matching. _ stands for any one character, and percentage stands for zero or more arbitrary characters. The UNION clause can be used to compute the union of any two union compatible sets of tuples, which are themselves the result of SQL queries. Example, with Union. The UNION clause can be used to compute the union of any two union compatible sets of tuples which are themselves the result of SQL query. SELECT, FROM, WHERE, AND, UNION, SELECT, FROM, WHERE, AND. The same query can be resolved also by the OR clause. In this case we have SELECT, FROM, WHERE, AND, OR. In the case of nested queries, if we need to find names of sailors who've reserved boat 103, the query should be like; SELECT, FROM, WHERE, IN, FROM, WHERE. The inner subquery has been completely independent of the outer query. A very powerful feature of SQL, a WHERE clause, can itself contain a SQL query. Actually, SQL form are HAVING clauses. Well, at this point we know how to insert, delete, and query a database. See you soon.
