COP4710: Midterm I Review***

SQL: Would "SELECT * FROM Student WHERE sname LIKE 'SMITH%'" find someone whose name was:

(i) "LA SMITH" --NO (ii) "SMITH-JONES" --YES (iii) "SMITH JR." --YES (iv) "SMITH, JR" --YES

SQL: Which clause[s] can be used in place of the JOIN in Server SQL?

WHERE

SQL: How many rows would you get in this meaningless, triple Cartesian product (use COUNT(*))?

SELECT COUNT(*) FROM T1,T2, T3;

SQL: What is the default order of an ORDER BY clause?

ascending

SQL: Using the COUNT feature, determine whether there are duplicate names in the Student table.

• There is one duplicate name:

SQL: The point about ignoring nulls can be illustrated by the following query, which also shows that several aggregate functions can be placed in the result set:

SELECT AVG(hours), MAX(wage), COUNT(hours) FROM New_Employee; The nulls are ignored by the functions. Also note that where COUNT(*) counts all the rows in a result set, COUNT(hours) counts only those rows where hours is non-null.

SQL: Another way to comment in SQL is to use____. Following is an example of a commented statement that uses this format:

/* ... */ SQL>SELECT sname, class /* get the sname and class */ FROM rearp.Student /* from the Student table */ ; In some versions of Oracle you cannot start a statement with a comment as your first word because you need a keyword such as SELECT for correct syntax.

SQL: What kind of comparison operators can be used in a WHERE clause?

> < >= <= = <>

SQL: The following statement: SELECT COUNT(*) FROM Student, Grade_report, Section WHERE student.stno=Grade_report.student_number--join Student to Grade_report AND grade_report, section_id=Section.section_id--join Grade_report to Section

A COUNT of a three-way equi-join rather than a three-way Cartesian product.

SQL: Data-types

A data-type of an attribute defines the allowable values as well as the operations we can perform on the attri- bute. We commonly use the NUMBER data-type for numbers and the CHAR and VARCHAR2 data-types for character strings. In this section, we will explore these and other commonly and uncommonly used data-types.

SQL: The XML data-type

A new SQL data-type, XMLType, has been created by Oracle to handle XML data. XML is a standardized tex- tual coding technique used to exchange data over the internet. Why does Oracle need an XML datatype? The answer is that some developers create and exchange data as XML and if Oracle did not have an XML datatype, then to handle the XML data in an Oracle database, there would have to be a bridge built to transform the XML data to a common SQL datatype or vice-versa. As with other data-types, XMLType can be used as a data-type for a column of a table or view. The maximum size of an XMLType attribute is 4 gigabytes. Using XML within SQL is beyond the scope of this material. Using XML involves data definition docu- ments, style sheets and other ancillary tools. The conversion of data to and from XML involves using CLOB data-types in SQL and SQL procedures specifically designed to handle this new and exciting datatype.1

SQL: What is the ORDER BY used for?

A relational database contains sets of rows of data and sets are not ordered. If you wish to display the contents of a table in a predictable manner, you may use the ORDER BY clause in the SELECT statement.

SQL: Table Aliases and introducing multi-table joins

A table alias is a temporary variable-name for a table that allows us to short-hand the notation as we qualify attributes. Here is an example of a one-letter table alias: SELECT * FROM Emps e, Jobs j -- table aliases follow the named table in FROM WHERE e.jobcode = j.jobc; This table alias is defined by a letter after the table name, so the table alias for Jobs here is j and the table alias for Emps in this example is e. Some people prefer a short, meaningful word or expression rather than a one-letter table alias, but the one-letter alias is very common among SQL users. We will use many table aliases in future statements and in all multi-table joins and queries. Here is the same join in ANSI form with aliases (and it produces the same result as above): SELECT * FROM Emps e INNER JOIN Jobs j -- note the table aliases ON e.jobcode = j.jobc; Following are examples showing statements with and without table aliases: Without table aliases: SELECT Student.stno, Section.course_num, Grade_report.grade FROM Student, Grade_report, Section WHERE Student.stno = Grade_report.student_number AND Grade_report.section_id = Section.section_id; With table aliases: SELECT stu.stno, sec.course_num, gr.grade FROM Student stu, Grade_report gr, Section sec WHERE stu.stno = gr.student_number AND gr.section_id = sec.section_id; As with the one-letter example, the table aliases stu, gr, and sec, are declared just after the table name in the FROM part of the SELECT. In multi-table queries, it is not advisable to leave off qualifiers for attributes even if the database is well known. Most commonly, qualifiers are handled with table aliases. One never knows when the database will be expanded or when a query must be analyzed by another person. Aliases are not persistent; they are only for the current statement or query. That is, table aliases are not saved after the query is run (unless the query itself is saved). As with many Oracle users, we will use single-letter table aliases in our future examples.

SQL: What are four major operators that can be used to combine conditions on a WHERE clause? Explain the operators with examples.

AND, OR, BETWEEN, NOT BETWEEN

SQL: Is COUNT an aggregate function or a row-level function? Explain why. Give at least one example of when the COUNT function may come in handy. Does the COUNT function take nulls into account?

Aggregate function. SELECT COUNT(*) FROM TABLENAME; NO.

SQL: What are aggregate functions? Give examples of aggregate functions. What is another term for an aggregate function?

An aggregate function (or group function) is a function that returns a result (one number) after calculations based on multiple rows. COUNT, SUM, AVG, MAX, and MIN

SQL: What is an INNER JOIN?

An inner join is a join in which the values in the columns being joined are compared using a comparison operator.

SQL: Large Object (LOB) Data-types (rarely used except in very specific circumstances)

As of Oracle 8, four new large object (LOB) data-types are supported: BFILE and three LOB data-types— BLOB, CLOB, and NCLOB. BFILE is an external LOB data-type that only stores a locator value that points to the external binary file. BLOB is used for binary large objects, CLOB is used for large character objects, and NCLOB is a CLOB data-type for multi-byte character sets. Data in the BLOB, CLOB, or NCLOB data-types is stored in the database, although LOB data does not have to be stored with the rest of the table. Single LOB columns can hold up to 4Gb in length and multiple LOB columns are allowed per table. In addition, Oracle allows you to specify a separate storage area for LOB data, greatly simplifying table sizing and data administration activities for tables that contain LOB data. Note that LOB data-types consume large quantities of memory.

SQL: VARCHAR2 Data-type (very commonly used)

As we mentioned earlier in the chapter, VARCHAR2 (pronounced "var-care") is Oracle's variable-length char- acter data-type. For this data-type, maximum lengths are specified, as in VARCHAR2(20), for a string of zero to twenty characters. When varying sizes of data are stored in an Oracle VARCHAR2, only the necessary amount of storage is allocated. This practice makes the internal storage of Oracle data more efficient. In fact, some Oracle practitioners suggest using only VARCHAR2(n) instead of CHAR(n). The minimum storage size for VARCHAR2 is 1 byte; the maximum size is 4000 bytes. Since there is no default size for VARCHAR2, you must specify a size. ***Begin Note*** 59 Older versions of Oracle and other SQLs used VARCHAR (without the "2") instead of VARCHAR2. VARCHAR may not be supported in future versions, so we advise you to use VARCHAR2 instead. ***End Note***

SQL: What is a join? Why do you need a join?

By using joins, you can retrieve data from two or more tables based on logical relationships between the tables

SQL: CHAR Data-type (commonly used)

CHAR (pronounced "care") is a fixed-length character data-type. This data-type is normally used when the data will always contain a fixed number of characters. For example, suppose major codes are always exactly four characters long; they should be encoded as CHAR(4). Social security numbers are also good candidates for this data-type because they always contain nine digits, so CHAR(9) can be used. Although Social Security (SS) numbers are digits, they are not used for calculation; hence, SS numbers may be stored as characters. In a field defined as CHAR, if the requisite number of characters is not inserted, the attribute will be padded on the right with blanks. For example, if we defined the social security number as CHAR(10) instead of CHAR(9), there would be one blank space on the right of every 9-digit social security number character string. The default (that is, the minimum size) for CHAR is 1 byte; and its maximum size is 2000 bytes.

SQL: The___ with an asterisk as the argument is an aggregate function that returns a count of the number of rows in the result set.

COUNT

SQL: When dealing with multiple tables, it is often desirable to explore the result set without actually displaying all of it. For example, we may want to know how many rows there are in a result set without actually seeing the result set itself. The "row-counter" in SQL is a function called ___.

COUNT

SQL: Tablename and attribute names are your choice, but keywords should be avoided (words like table or select). A data-type defines the kind of data that is allowable for that attribute - like numbers, alphanumeric characters or dates. We will be creating a table called Customer. Suppose the table has two attributes: customer number (we choose to abbreviate this as cno) and balance. The cno attribute is a fixed-length char- acter attribute with a length of 3 and will represent the customer-number. The balance attribute is numeric with five digits and no decimals. The appendage "DEFAULT 0" means that if no value is specified for balance when rows are inserted into the table, balance will be set equal to zero.

CREATE TABLE Customer (cno CHAR(3), balance NUMBER(5) DEFAULT 0); In this example, if "DEFAULT 0" were not used, balance would default to NULL if no value for balance was supplied. NULL means "empty" and is Oracle's way of signifying that no value is present.

SQL: The UPDATE Statement Another common statement used for setting/changing data values in tables is the UPDATE statement. As with the INSERT INTO .. SELECT option, you often update more than one row at a time with UPDATE. To illustrate the UPDATE statement, you may create a table called Customer2, like this:ed.

CREATE TABLE Customer2 (cno CHAR(3), balance NUMBER(5), date_opened DATE); Now suppose some values are inserted into the table using one of the above techniques. And then suppose that you would like to set all balances in the new table to zero. You can do this with an UPDATE statement, as follows: UPDATE Customer2 SET balance = 0; This statement sets all balances in all rows of the table to zero, regardless of their previous value. ***Begin Warning*** Beware—this can be a dangerous. Later in the chapter we will discuss a method in which to safeguard against accidental misuse using the ROLLBACK statement. ***End Warning*** It is often useful and appropriate to include a WHERE clause on the UPDATE statement so that values are set selectively. For example, the updating of a particular customer in our new table, Customer2, might be done with the following statement: UPDATE Customer2 SET balance = 0 -- this updates only one customer WHERE cno = 101; This would update only customer 101's row(s). You could also set specific balances to zero with other criteria in the WHERE clause with a statement like the following: UPDATE Customer2 SET balance = 0 WHERE date > '01-JAN-19'; However, in this last example, multiple rows might be updated.

SQL: If you created a table with n attributes, you usually would have n values in the INSERT INTO .. VALUES part of the command. For example, if you have created a table called Employee, like this:

CREATE TABLE Employee (name VARCHAR2 (20), address VARCHAR2 (20), employee_number NUMBER (3), salary NUMBER (6,2)); then the INSERT INTO .. VALUES to insert a row would match column for column and would look like this: INSERT INTO Employee VALUES ('Joe Smith', '123 4th St.', 101, 2500); The values in the VALUES part of the statement correspond to the attribute names by their ordering. Note that character types must be enclosed in single quotes and numeric types are not in quotes. 'Joe Smith' corresponds to the attribute, name, and 2500 corresponds to salary. An INSERT that looks like the following is incorrect because it does not include all four attributes of the Employee table: INSERT INTO Employee VALUES ('Joe Smith', '123 4th St.'); However, if you do not have data values for all four attributes of the Employee table, and you wish to insert values into only two of the four attributes, you can name the attributes you want to insert and provide values for only those attributes you name. For example, you can use an INSERT like this: INSERT INTO Employee (name, address) VALUES ('Joe Smith', '123 4th St.'); In this case, the inserted row will contain NULL values for the attributes you did not use. An INSERT that looks like the following is incorrect because it does not have the values in the same order as the definition of the table: INSERT INTO Employee VALUES (2500, 'Joe Smith', 101, '123 4th St.'); If the data had to be specified in this order, the statement could be corrected by specifying the column names like this: INSERT INTO Employee (salary, name, employee_number, address) VALUES (2500, 'Joe Smith', 101, '123 4th St.'); The following INSERT would also be legal if the address and the salary were unknown when the row was created, provided that the address and salary attributes allowed nulls: INSERT INTO Employee VALUES ('Joe Smith', null, 101, null); For example, you can use an INSERT like this: INSERT INTO Employee (name, address) VALUES ('Joe Smith', '123 4th St.'); In this case, the inserted row will contain NULL values for the attributes you did not use. An INSERT that looks like the following is incorrect because it does not have the values in the same order as the definition of the table: INSERT INTO Employee VALUES (2500, 'Joe Smith', 101, '123 4th St.'); If the data had to be specified in this order, the statement could be corrected by specifying the column names like this: INSERT INTO Employee (salary, name, employee_number, address) VALUES (2500, 'Joe Smith', 101, '123 4th St.'); The following INSERT would also be legal if the address and the salary were unknown when the row was created, provided that the address and salary attributes allowed nulls: INSERT INTO Employee VALUES ('Joe Smith', null, 101, null); If you use non-numeric types like CHAR (fixed character size) or VARCHAR2 (variable character size), you must use single quotes in the INSERT statement. If you use numbers, you should not use quotes. Oracle will convert character strings to numbers, but it is never good to let a system do something that you should do yourself.

SQL: We could have also used other data-types for the attributes. For example, another common character datatype is VARCHAR2(n), which is a variable-length character string of length n. In this example, we are creating a table of names:

CREATE TABLE Names (name VARCHAR2(20)); This table, Names, has one attribute called name. Name is of data-type VARCHAR2 (which means varying length character), and each name in the table can have a maximum size of 20 characters. Older versions of SQL used a data-type called VARCHAR, but Oracle now uses and recommends the use of VARCHAR2.

SQL: In a WHERE clause, do you need to enclose a text column in quotes? Do you need to enclose a numeric column in quotes?

Character or text value should be in single quotes, but the numeric value should not be in single quotes. Column name could be in Double quotes or brackets.

SQL: Adding Comments to Statements.

Comments are ignored by SQLPLUS, but are very useful to programmers in determining what a statement does, when it was written, who wrote it, and so on. Comments can be put into multi-line statements like this: SQL>SELECT * -- comments ... statement shows all columns 19 2 FROM rearp.Student -- from the Student table 3 WHERE class = 4;

SQL: Use the BETWEEN operator to list all the sophomores, juniors, and seniors from the Student table.

SELECT * FROM Student WHERE class BETWEEN 2 AND 4 ORDER BY class Sophomores 2, juniors 3, and seniors 4

SQL: Use the NOT BETWEEN operator to list all the sophomores and juniors from the Student table.

SELECT * FROM Student WHERE class NOT BETWEEN 1 AND 1 AND class NOT BETWEEN 4 AND 4 ORDER BY class

SQL: The DATE Data-type and Type

Conversion Functions (commonly used) A DATE data-type allows the storage and manipulation of dates and times. The time part of the data-type is typically ignored, but it is defined as part of the data-type. There are functions to add, take differences between dates, convert to a four digit year, and so on. DATE data-types are defined in a manner similar to what we have seen. Here is an example of a table containing a DATE data-type: CREATE TABLE date_example (day_test DATE, amount NUMBER(6,2), name VARCHAR2(20)); Data is entered into the day_test date attribute, in the character format 'dd-Mon-yy,' which automatically converts the character string to a date format. ***Begin Note*** The format of the DATE data-type can be changed by the DBA (Data Base Administrator), but dd-Mon-yy is commonly used. ***End Note*** Some examples of inserts for the date_example table would be: INSERT INTO date_example (day_test) VALUES ('10-oct-19') /* valid */ INSERT INTO date_example (day_test) VALUES ('10-OCT-19') /* valid (month not case sensitive) */ INSERT INTO date_example (day_test) VALUES (10-oct-19) /* invalid (needs quotes) */ INSERT INTO date_example (day_test) VALUES (sysdate) /* valid (system date) */ INSERT INTO date_example (day_test) VALUES ('10-RWE-19') /* invalid (bad month) */ INSERT INTO date_example (day_test) VALUES ('32-OCT-19') /* invalid (bad day) */ INSERT INTO date_example (day_test) VALUES ('31-OCT-19') /* valid */ INSERT INTO date_example (day_test) VALUES ('31-SEP-19') /* invalid (bad day— 61 Oracle keeps up with the correct days per month) */ For other than "standard" dates in the form 'dd-Mon-yy', the TO_DATE function can be used to insert values in other ways. The TO_DATE function has two arguments: TO_DATE (a,b), where "a" is the string you are using to enter the date and "b" is a recognizable Oracle character format.

SQL: Display the building number, room number, and room capacity of all rooms in descending order by room capacity. Use appropriate column aliases to make your output more readable.

SELECT BLDG as [Building No], room as [Room No], Capacity FROM Room ORDER BY capacity DESC

SQL: The DELETE Statement The DELETE statement is used to delete rows from tables. A sample syntax for the DELETE statement is:

DELETE FROM Table WHERE (condition) The (condition) determines which rows to delete. ***Begin Warning*** With UPDATE and DELETE, multiple rows can be affected and hence these can be dangerous commands. Be careful when using them and please wait until you learn to use ROLLBACK before applying these statements. ***End Warning*** An example of a multi-row delete from our sample Customer2 table might be: DELETE FROM Customer2 WHERE balance < 10; or DELETE FROM Customer2 WHERE date_opened < '01-JAN-17';

SQL: Deleting a table To remove a table from the database, you would use the DROP TABLE command as follows:

DROP TABLE Table_names; Once you drop a table, you cannot bring the table or its data back. DROPping tables cannot be undone.

SQL: Three common ways to populate tables:

INSERT INTO .. VALUES INSERT INTO .. SELECT SQLLOADER (for bulk loading of larger tables - we do this later)

SQL: Inserting Values into a Created Table Values may be inserted into a created table using several methods. We will illustrate two of the three common ways to populate tables:

INSERT INTO .. VALUES INSERT INTO .. SELECT SQLLOADER (for bulk loading of larger tables - we do this later) In this chapter we will look at INSERT INTO .. VALUES and INSERT INTO .. SELECT. We will discuss SQLLOADER later in the text because SQLLOADER is not a command per se, but rather a special Oracle procedure for loading tables.

SQL: INSERT INTO .. VALUES The INSERT INTO with the VALUES option is a way of creating one row of a table. The following example inserts one row into the Names table:

INSERT INTO Names VALUES ('Joe Smith'); where 1. INSERT is the name of the command. 2. INTO is a necessary keyword. 3. "Names" is the name of the existing table. 4. VALUES is another necessary keyword. 5. 'Joe Smith' is a string of letters in agreement with the data-type Note that 'Joe Smith' is surrounded by single quotes. "Joe Smith" would be invalid.

SQL: INSERT INTO .. SELECT With the INSERT INTO ..VALUES option, you insert only one row at a time into a table. With the INSERT INTO .. SELECT option, you may (and usually do) insert many rows into a table at one time. The syntax of the INSERT INTO .. SELECT is:

INSERT INTO Table-name "SELECT clause" For example, the following statement will insert all the values from the table Customer into another table called Newcustomer: INSERT INTO Newcustomer SELECT * FROM Customer; Before using this above INSERT INTO .. SELECT statement, you would have had to first create the Newcustomer table; and, though the attributes of Newcustomer do not have to be named exactly what they are named in Customer, the data-types and sizes have to match. The size of the attributes that you are inserting into, that is, the size of the attributes of Newcustomer, have to be at least as big as the size of the attributes of Customer. You may limit the SELECT and load to less than the whole table—fewer rows or columns, as necessary. Some examples of restricted SELECTs for the INSERT statement follow. Suppose you had a table with only one attribute, a name, which was created as follows: CREATE TABLE Namelist (customer_name VARCHAR2(20)); And assume that a second table existed that had the following structure: Customer1(cname, cnumber, amount) where cname is VARCHAR2(20). You can populate the Namelist table with the cname from the Customer1 table as follows: INSERT INTO Namelist SELECT cname FROM Customer1; This could copy all the names from Customer1 to Namelist. But, you do not have to copy all the names from Customer1 because you can restrict the SELECT as illustrated in the following example: INSERT INTO Namelist SELECT cname FROM Customer1 WHERE amount > 100 As with the INSERT INTO .. VALUES, if you create a table with n attributes, you usually would have n values in the INSERT INTO .. SELECT in the order of definition. You have a table like the following: Employee (name, address, employee_number, salary). Now suppose that you wanted to load a table called Emp1 from Employee with the following attributes, where the attributes stand for address, salary, and employee number, respectively: Emp1 (addr, sal, empno) As with INSERT INTO .. VALUES, the INSERT INTO .. SELECT with no named attributes must match column for column and would look like the following: INSERT INTO Emp1 SELECT address, salary, employee_number FROM Employee; The following INSERT would fail because the Employee table has four attributes while the Emp1 table has only three: INSERT INTO Emp1 SELECT * FROM Employee; The following INSERT would also fail because the attribute order of the SELECT must match the order of definition of attributes in the Emp1 table: INSERT INTO Emp1 SELECT address, employee_number, salary FROM Employee; As you might guess from the last INSERT INTO .. SELECT example, you can load fewer attributes than the whole row of the Emp1 table using named attributes in the INSERT with a statement like: INSERT INTO Emp1 (address, salary) SELECT address, salary FROM Employee; However, this would leave the other attribute, employee_number, with a value of NULL or with a default value. Therefore, although loading less than a "full row" is syntactically correct, you must be aware of the result. One final point: INSERT INTO .. SELECT could succeed if the data-types of the SELECT matched the data-types of the attributes in the table to which you are INSERTing. For example, if you had another table called Emp2 with name, address as attributes (both defined as VARCHAR2), and if you executed the following, the statement could succeed, but you would have an address in a name attribute and vice versa: INSERT INTO Emp2 SELECT address, name FROM Employee; We say "could" here because there are ways to prevent integrity violations of this type, but we have not introduced them yet. Be careful with this INSERT INTO .. SELECT statement. Unlike INSERT INTO ..VALUES, which inserts one row at a time, you almost always insert multiple rows with INSERT INTO .. SELECT. If types match, the insert will take place regardless of whether it makes sense or not. Finally, the previous two statements (CREATE TABLE and INSERT INTO) may be combined for creating backup copies of tables like this: CREATE TABLE Course_copy AS SELECT * FROM COURSE You will get: Table created.

SQL: Abstract Data-types (sometimes used, but uncommon)

In Oracle, you can also define and use abstract data-types. A data-type defines a range of values and operations that can be performed on data declared to be of that type. An abstract data-type (ADT) defines the operations explicitly (in methods or procedures) and should allow you to only access data of that type via the defined method. ADTs are created with the CREATE TYPE statement.

SQL: What is an equi-join?.

Inner joins with an = operator are called equi-joins and joins with an operator other than an = sign are called non-equi-joins

SQL: NCHAR and NVARCHAR2 data-types (rarely used)

NCHAR stores fixed length character strings, and NVARCHAR2 stores variable length character strings, both of which are Unicode datatypes that correspond to the national character set. The character set of NCHAR and NVARCHAR2 data-types are specified at database creation time. The maximum length of an NCHAR column is 2000 bytes, and the maximum size of a NVARCHAR2 column is 4000 bytes. Unicode is an effort to have unified encoding of every character in every known language, that is, a data- base column that stores Unicode can store text written in any language. Oracle database users with global applications may need to use Unicode data for non-English characters.

SQL: Will the following statement work? SELECT COUNT (DISTINCT grade, section_id) FROM Grade_report;

NO. The syntax of SQL will not allow you to COUNT two columns with this query. Thus, the following query will not work:

SQL: Does ORDER BY actually change the order of the data in the tables or does it just change the output?

Only change the output.

SQL: Display an equi-join of the Stu and Major tables on majorCode. First do this using the INNER JOIN, and then display the results using the equi-join with an appropriate WHERE clause. Use appropriate table aliases. How many rows did you get?

SELECT * FROM Stu s INNER JOIN Major m ON s.majorCode = m.majorCode SELECT * FROM Stu s, Major m WHERE s.majorCode = m.majorCode 4 rows

SQL: Write, execute, and print a query to list student names and grades (just two attributes) using the table alias feature. Restrict the list to students that have either As or Bs in courses with ACCT prefixes only.Here's how to complete this problem: Get the statement to work as a COUNT of a join of the three tables, Student, Grade_report, Section. Use table aliases in the join condition. Note that a join of n tables requires (n - 1) join conditions, so here you have to have two join conditions: one to join the Student and Grade_report tables, and one to join the Grade_report and Section tables. Note the number of rows that you get (expect no more rows than is in the Grade_report table). Why do you get this result?

SELECT COUNT(*) FROM Grade_report g INNER JOIN Student st ON g.STUDENT_NUMBER = st.STNO INNER JOIN Section se ON g.SECTION_ID = se.SECTION_ID

SQL: Modify the query and put the Accounting condition in the WHERE clause. Note the number of rows in the resultit should be a good bit less than in question 3a.

SELECT COUNT(*) FROM Grade_report g INNER JOIN Student st ON g.STUDENT_NUMBER = st.STNO INNER JOIN Section se ON g.SECTION_ID = se.SECTION_ID WHERE se.COURSE_NUM LIKE 'ACCT%'

SQL: How many rows are there in the Cartesian product of the Student, Section, and Grade_report tables in our Student-Course database? This question can be answered by the following query:

SELECT COUNT(*) FROM Student, Section, Grade_report; The COUNT from this statement equals the product of the table sizes of the three tables.

SQL: The following is the syntax or the COUNT function and counts all the rows in a table:

SELECT COUNT(*) FROM table-name(s);

SQL: Display the COUNT of tuples (rows) in each of the tables Grade_report, Student, and Section. How many rows would you expect in the Cartesian product of all three tables? Display the COUNT (not the resulting rows) of the Cartesian product of all three and verify yourresult (use SELECT COUNT(*) ...).

SELECT COUNT(*) FROM Grade_report; SELECT COUNT(*) FROM Student; SELECT COUNT(*) FROM Section; SELECT COUNT(*) FROM Grade_report, Student, Section;

SQL: A statement to COUNT distinct grades, we could use:

SELECT COUNT(DISTINCT grade) FROM Grade_report; This result doe snot count null values. So the DISTINCT produces null values in the output, but the COUNT does not count the null values.

SQL: Display the COUNT of section-ids from the Section table. Display the COUNT of DISTINCT section-ids from the Grade_report table. What does this information tell you? (Hint: section_id is the primary key of the Section table.)

SELECT COUNT(SECTION_ID) FROM Section; SELECT COUNT(SECTION_ID) FROM Grade_report; SELECT COUNT(DISTINCT(SECTION_ID)) FROM Grade_report;

SQL: One of the more common things to ask of a database involves finding an aggregate function on a set of numeric values. We have seen the COUNT function already. The aggregate functions SUM, AVG, MIN, MAX, and others work in a similar way. For this example, suppose we have a table called New_Employee that looks like this:

SELECT SUM(Hours) FROM New_Employee; This SUM result is particularly interesting in that fields that contain null values are ignored by the SUM function (as they are by all aggregate numeric functions).

SQL: Display the course name and number of all courses that are three credit hours.

SELECT course_name, course_number FROM Course WHERE credit_hours = 3

SQL: Display all the course names and course numbers (from question 3) in ascending order by course name.

SELECT course_name, course_number FROM Course WHERE credit_hours = 3 ORDER BY course_name

SQL: List the offering_dept of all courses that are more than three credit hours.

SELECT course_name, course_number, offering_dept, credit_hours FROM Course WHERE credit_hours > 3 ORDER BY offering_dept

SQL: Display a list of all student names ordered by major.

SELECT sname as [Student Name], major as [Major] FROM student ORDER BY major

SQL: Display the student name and student number of all students who are juniors (hint: class = 3).

SELECT sname as [Student name], stno as [Student number] FROM Student WHERE class = 3

SQL: Display the student names and numbers (from question 2) in descending order by name.

SELECT sname as [Student name], stno as [Student number] FROM Student WHERE class = 3 ORDER BY sname DESC

SQL: The following statement will show the names (snames) and majors in the Student table, ordered by sname. Here the output will be ordered in ascending order of sname because ascending order is the default of the ORDER BY clause.

SELECT sname, major FROM Student ORDER BY sname;

SQL: To order the output in descending order, the keyword DESC can be appended to the appropriate attribute in the ORDER BY clause as follows:

SELECT sname, major FROM Student -- this time we ask for descending order ORDER BY sname DESC;

SQL: Again, modify the query and add the grade constraints. The number of rows should decrease again. Note that if you have WHERE x and y or z, parentheses are optional, but then the criteria will be interpreted according to precedence rules. The reason that we want you to "start small" and add conditions is that it gives you a check on what you ought to get and it allows you to output less nonsense. Your minimal starting point should be a count of the join with appropriate join conditions.

SELECT st.STNO, st.STNO, g.GRADE, se.COURSE_NUM FROM Grade_report g INNER JOIN Student st ON g.STUDENT_NUMBER = st.STNO INNER JOIN Section se ON g.SECTION_ID = se.SECTION_ID WHERE g.GRADE in ('A', 'B') AND se.COURSE_NUM LIKE 'ACCT%'

SQL: Simple CREATETABLE Statement

The CREATE TABLE statement allows us to create a table in which we can store data. A minimal syntax for this statement is as follows (we will expand this CREATE TABLE syntax with more options in a later chapter): CREATE TABLE Tablename (attribute_name data-type, attribute_name data-type, ...); Tablename and attribute names are your choice, but keywords should be avoided (words like table or select). A data-type defines the kind of data that is allowable for that attribute - like numbers, alphanumeric characters or dates.

SQL: How does the DISTINCT function work?

The DISTINCT function omits rows in the result set that contain duplicate data in the selected columns. DISTINCT can also be used as an option with aggregate functions like COUNT, SUM and AVG.

SQL: LONG, RAW, LONG RAW, and BOOLEAN Data-types (rarely used except in very specific circumstances)

The LONG data-type is similar to VARCHAR2 and has a variable length of up to 2Gb. However, there are some restrictions in the access and handling of LONG data-types: • Only one LONG column can be defined per table. • LONG columns may not be used in subqueries, functions, expressions, WHERE clauses, or indexes. A RAW or LONG RAW data-type is used to store binary data such as graphics characters or digitized pictures. The maximum size for RAW is 2000 bytes while the maximum size for LONG RAW is 2 gigabytes. Thus, LONG RAW allows for larger sets of binary data. In Oracle, there is also a BOOLEAN data-type with values TRUE, FALSE, and NULL, but it is not often used. ***Begin Note*** The BOOLEAN data-type is only available when running the procedural language (PL/SQL). We will discuss PL/SQL in Chapter 11. ***End Note***

SQL: When would you use the ROWCOUNT function versus using the WHERE clause?

The WHERE clause assumes that you have knowledge of the actual data values present in a data set. ROWCOUNT returns only a sample of a result set, and you have no idea which range of values are present in the table.

SQL: The WHERE CLAUSE

The WHERE clause in this example is a row filter. The result set in this query consists of rows in the table, Student, with all columns (SELECT *), but only the rows WHERE the value of the class attribute is equal to 4 (that is, seniors) are selected for the result set.

SQL: What are the logical operators?

The logical operators are AND, OR, and NOT. AND and OR are used to connect search conditions in WHERE clauses. NOT reverses the result of a search condition. AND joins two conditions and returns TRUE only when both conditions are true. OR also connects two conditions, but it returns TRUE when either of the conditions is true.

SQL: Common Number Data-types

The most commonly used numeric data-types in Oracle are NUMBER and INTEGER. The NUMBER data- type, with no parentheses, defaults to a number that is up to 38 digits long with 8 decimal places. NUMBER may also be defined as having some maximum number of digits, such as NUMBER(5). Here, the (5) is referred to as the "precision" of the datatype and may be from 1 to 38. If a second number is included in the definition, for example NUMBER (12,2), the second number is called the "scale." The second number defines how many digits will come after a decimal point. Here, with NUMBER (12,2) we may have up to ten digits before the decimal point and two after. A very common datatype used in programming languages is type INTEGER. INTEGER holds whole numbers and is equivalent to NUMBER(38). Usually, you enter a precision and/or a scale for your numbers with entries such as NUMBER(3) or NUMBER(6,2). The NUMBER(3) implies you will have three digits and no decimal places. NUMBER(6,2) means that the numbers you store will be similar to 1234.56 or 12.34, with a decimal before the last two digits in a field that has a maximum of six numbers overall. Here is an example to illustrate precision and scale with a numeric attribute. Type the following: CREATE TABLE Testnum (x NUMBER(5,2)); Here we are creating a one column table with a numeric column called "x." INSERT INTO Testnum VALUES (20); INSERT INTO Testnum VALUES (200); INSERT INTO Testnum VALUES (2000); The first two INSERTs work fine, but the last INSERT gives an error: INSERT INTO Testnum VALUES (2000); * ERROR at line 1: ORA-01438: value larger than specified precision allows for this column - Then try typing: INSERT INTO Testnum VALUES (200.12); INSERT INTO Testnum VALUES (200.123); Now, SELECT * FROM Testnum; Will give: X ---------- 20 200 200.12 200.12 If a number is inserted that is too large for the precision, an error results. If a number with too many decimal places is added, the decimal values beyond the scale are rounded up automatically, as shown by the following inserts: INSERT INTO Testnum VALUES (123.99778); INSERT INTO Testnum VALUES (333.333); INSERT INTO Testnum VALUES (555.499999); INSERT INTO Testnum VALUES (666.500004); Now, SELECT * FROM Testnum; Gives: X ---------- 20 200 200.12 200.12 124 333.33 555.5 666.5 In addition to the above numeric datatypes, there are other specialty numeric types (SMALLINT, BINARY DOUBLE and others). There are also the FLOAT data-types, which allow large exponential numbers to be stored, but they are rarely used. Float datatypes include FLOAT, REAL and DOUBLE PRECISION.

SQL: Using ORDER BY

To show the contents of a table in a specific order, we can force the ordering of the result set using the ORDER BY clause in the SELECT.

SQL: True or False: "-- comment..." cannot be on the same line with the semicolon.

True

SQL: Does Server SQL allow an expression like COUNT(DISTINCT column_name)?

Yes

SQL: The following statement: SELECT COUNT(class) FROM Students;

You will see the occurrence of non-null attributes. The COUNT(CLASS) will count the rows where class is not null.

SQL: The following statement: SELECT* From Students;

You would see all the rows of the Student Table plus the values for all the columns in those rows.

SQL: The following Statement: SELECT COUNT(*) From Student;

You would see the number of rows in the result set.

SQL: The ___ order is the default order of the ORDER BY clause.

ascending

SQL: Does it appear that there are any courses at all that are in the Grade_report, Section, or Course tables that are not in the others?

• All the courses in Grade_report table are in the Course table and Section table. There is one course that only is in the Section table, but not the Grade_report table. There are 12 more course in the Course table than those in the Section table.

SQL: Would you call UPPER or LOWER an aggregate function? Why or why not?

• No, an UPPER or LOWER, are not aggregates. The reason being, a function that returns a result based on multiple rows is called an aggregate function. We are looking at one row, not multiple rows. The UPPER function converts strings to all uppercase for display and testing (the actual data in the database is unaffected). If the data in the database is in mixed case or, more usually, if you do not know what the case is, you can add UPPER to a query. Or, alternatively, you can add LOWER to a query.

SQL: Using the COUNT feature, determine whether there are duplicate student numbers in the Student table.

• There are NO duplicate student numbers:

SQL: Again, using COUNTs, are there any math courses in the Course table that are not in the Section table?

• There are three courses in the Course table that are not in the Section table.

SQL: From the COUNT of courses, does it appear that there are any math courses in the Section table that are not in the Course table?

• Yes, it does appear that all the course in the Section table are in the Course table.