1 / 59

Basic SQL

Basic SQL. CREATE/DROP/ALTER TABLE. Data types : char, varchar, decimal, date CREATE TABLE DEPARTMENT ( DNAME VARCHAR(10) NOT NULL, DNUMBER INTEGER NOT NULL, MGRSSN CHAR(9), MGRSTARTDATE CHAR(9) ); DROP TABLE DEPENDENT; ALTER TABLE : three options ADD, MODIFY,DROP a column

wilton
Download Presentation

Basic SQL

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Basic SQL

  2. CREATE/DROP/ALTER TABLE • Data types : char, varchar, decimal, date • CREATE TABLE DEPARTMENT ( DNAME VARCHAR(10) NOT NULL, DNUMBER INTEGER NOT NULL, MGRSSN CHAR(9), MGRSTARTDATE CHAR(9) ); • DROP TABLE DEPENDENT; • ALTER TABLE : three options ADD, MODIFY,DROP a column • ALTER TABLE EMPLOYEE ADD JOB VARCHAR(12); • The database users must still enter a value for the new attribute JOB for each EMPLOYEE tuple. • This can be done using the UPDATE command. • Features added in SQL-99: Create Schema, and Referential Integrity Options

  3. REFERENTIAL INTEGRITY OPTIONS • We can specify RESTRICT, CASCADE, SET NULL or SET DEFAULT on referential integrity constraints (foreign keys) CREATE TABLE DEPT ( DNAME VARCHAR(10) NOT NULL, DNUMBER INTEGER NOT NULL, MGRSSN CHAR(9), MGRSTARTDATE CHAR(9), PRIMARY KEY (DNUMBER), UNIQUE (DNAME), FOREIGN KEY (MGRSSN) REFERENCES EMPON DELETE SET DEFAULT ON UPDATE CASCADE);

  4. REFERENTIAL INTEGRITY OPTIONS (cont’d) CREATE TABLE EMP(ENAME VARCHAR(30) NOT NULL,ESSN CHAR(9),BDATE DATE,DNO INTEGER DEFAULT 1,SUPERSSN CHAR(9),PRIMARY KEY (ESSN),FOREIGN KEY (DNO) REFERENCES DEPT ON DELETE SET DEFAULT ON UPDATE CASCADE,FOREIGN KEY (SUPERSSN) REFERENCES EMP ON DELETE SET NULL ON UPDATE CASCADE);

  5. Retrieval Queries in SQL • SQL has one basic statement for retrieving information from a database; the SELECT statement • This is not the same as the SELECT operation of the relational algebra • Important distinction between SQL and the formal relational model: • SQL allows a table (relation) to have two or more tuples that are identical in all their attribute values • Hence, an SQL relation (table) is a multi-set (sometimes called a bag) of tuples; it is not a set of tuples • SQL relations can be constrained to be sets by specifying PRIMARY KEY or UNIQUE attributes, or by using the DISTINCT option in a query

  6. Retrieval Queries in SQL (contd.) • Basic form of the SQL SELECT statement is called a mapping or a SELECT-FROM-WHERE block SELECT <attribute list> FROM <table list> WHERE <condition> • SQL queries walk through : Q0, Q1, Q2, Q8 (usage of alias), Q9, Q10, usage of ‘*’ operator in select list, Q11A • Set Operations : UNION, MINUS, INTERSECT. Examples : Q4

  7. NESTING OF QUERIES • Query 1: Retrieve the name and address of all employees who work for the 'Research' department. Q1: SELECT FNAME, LNAME, ADDRESS FROM EMPLOYEE WHERE DNO IN (SELECT DNUMBER FROM DEPARTMENT WHERE DNAME='Research' ) • The comparison operator IN compares a value v with a set (or multi-set) of values V, and evaluates to TRUE if v is one of the elements in V

  8. NULLS IN SQL QUERIES • SQL allows queries that check if a value is NULL (missing or undefined or not applicable) • SQL uses IS or IS NOT to compare NULLs because it considers each NULL value distinct from other NULL values, so equality comparison is not appropriate. • Query 14: Retrieve the names of all employees who do not have supervisors. Q14: SELECT FNAME, LNAME FROM EMPLOYEE WHERE SUPERSSN IS NULL • Note: If a join condition is specified, tuples with NULL values for the join attributes are not included in the result

  9. Joined Relations Feature in SQL2 • Examples: Q1: SELECT FNAME, LNAME, ADDRESS FROM EMPLOYEE, DEPARTMENT WHERE DNAME='Research' AND DNUMBER=DNO • could be written as: Q1: SELECT FNAME, LNAME, ADDRESS FROM (EMPLOYEE JOIN DEPARTMENT ON DNUMBER=DNO) WHERE DNAME='Research’ • or as: Q1: SELECT FNAME, LNAME, ADDRESS FROM (EMPLOYEE NATURAL JOIN DEPARTMENT AS DEPT(DNAME, DNO, MSSN, MSDATE) WHERE DNAME='Research’

  10. AGGREGATE FUNCTIONS • Include COUNT, SUM, MAX, MIN, and AVG • Query 15: Find the maximum salary, the minimum salary, and the average salary among all employees. Q15: SELECT MAX(SALARY), MIN(SALARY), AVG(SALARY) FROM EMPLOYEE • Some SQL implementations may not allow more than one function in the SELECT-clause

  11. GROUPING (contd.) • Query 20: For each department, retrieve the department number, the number of employees in the department, and their average salary. Q20: SELECT DNO, COUNT (*), AVG (SALARY) FROM EMPLOYEE GROUP BY DNO • Query 21: For each project, retrieve the project number, project name, and the number of employees who work on that project. Q21: SELECT PNUMBER, PNAME, COUNT (*) FROM PROJECT, WORKS_ON WHERE PNUMBER=PNO GROUP BY PNUMBER, PNAME

  12. THE HAVING-CLAUSE • Query 22: For each project on which more than two employees work, retrieve the project number, project name, and the number of employees who work on that project. Q22: SELECT PNUMBER, PNAME, COUNT(*) FROM PROJECT, WORKS_ON WHERE PNUMBER=PNO GROUP BY PNUMBER, PNAME HAVING COUNT (*) > 2

  13. SUBSTRING COMPARISON • The LIKE comparison operator is used to compare partial strings • Two reserved characters are used: '%' (or '*' in some implementations) replaces an arbitrary number of characters, and '_' replaces a single arbitrary character • Query 25: Retrieve all employees whose address is in Houston, Texas. Here, the value of the ADDRESS attribute must contain the substring 'Houston,TX‘ in it. Q25: SELECT FNAME, LNAME FROM EMPLOYEE WHERE ADDRESS LIKE '%Houston,TX%'

  14. ARITHMETIC OPERATIONS • The standard arithmetic operators '+', '-'. '*', and '/' (for addition, subtraction, multiplication, and division, respectively) can be applied to numeric values in an SQL query result • Query 27: Show the effect of giving all employees who work on the 'ProductX' project a 10% raise. Q27: SELECT FNAME, LNAME, 1.1*SALARY FROM EMPLOYEE, WORKS_ON, PROJECT WHERE SSN=ESSN AND PNO=PNUMBER AND PNAME='ProductX’

  15. ORDER BY • The ORDER BY clause is used to sort the tuples in a query result based on the values of some attribute(s) • Query 28: Retrieve a list of employees and the projects each works in, ordered by the employee's department, and within each department ordered alphabetically by employee last name. Q28: SELECT DNAME, LNAME, FNAME, PNAME FROM DEPARTMENT, EMPLOYEE, WORKS_ON, PROJECT WHERE DNUMBER=DNO AND SSN=ESSN AND PNO=PNUMBER ORDER BY DNAME, LNAME • The default order is in ascending order of values

  16. INSERT • Example: U1: INSERT INTO EMPLOYEE VALUES ('Richard','K','Marini', '653298653', '30-DEC-52', '98 Oak Forest,Katy,TX', 'M', 37000,'987654321', 4 ) • An alternate form of INSERT specifies explicitly the attribute names that correspond to the values in the new tuple • Attributes with NULL values can be left out • Example: Insert a tuple for a new EMPLOYEE for whom we only know the FNAME, LNAME, and SSN attributes. U1A: INSERT INTO EMPLOYEE (FNAME, LNAME, SSN) VALUES ('Richard', 'Marini', '653298653')

  17. INSERT (contd.) • Example: Suppose we want to create a temporary table that has the name, number of employees, and total salaries for each department. • A table DEPTS_INFO is created by U3A, and is loaded with the summary information retrieved from the database by the query in U3B. U3A: CREATE TABLE DEPTS_INFO (DEPT_NAME VARCHAR(10), NO_OF_EMPS INTEGER, TOTAL_SAL INTEGER); U3B: INSERT INTO DEPTS_INFO (DEPT_NAME, NO_OF_EMPS, TOTAL_SAL) SELECT DNAME, COUNT (*), SUM (SALARY) FROM DEPARTMENT, EMPLOYEE WHERE DNUMBER=DNO GROUP BY DNAME ;

  18. DELETE • Examples: U4A: DELETE FROM EMPLOYEE WHERE LNAME='Brown’ U4B: DELETE FROM EMPLOYEE WHERE SSN='123456789’ U4C: DELETE FROM EMPLOYEE WHERE DNO IN (SELECT DNUMBER FROM DEPARTMENT WHERE DNAME='Research') U4D: DELETE FROM EMPLOYEE

  19. UPDATE • UPDATE tablenameSET columnname = expression [, columname = expression][WHERE conditionlist]; • Referential integrity should be enforced • Example: Give all employees in the 'Research' department a 10% raise in salary. U6: UPDATE EMPLOYEE SET SALARY = SALARY *1.1 WHERE DNO IN (SELECT DNUMBER FROM DEPARTMENT WHERE DNAME='Research')

  20. JDBC – Java Database Connectivity Modified slides from Dr. Yehoshua Sagiv

  21. Introduction to JDBC • JDBC is used for accessing databases from Java applications • Information is transferred from relations to objects and vice-versa • databases optimized for searching/indexing • objects optimized for engineering/flexibility

  22. We will use this one… JDBC Architecture Oracle Driver Oracle Java Application DB2 Driver JDBC DB2 Network MySQL Driver MySQL

  23. JDBC Architecture (cont.) Application JDBC Driver • Java code calls JDBC library • JDBC loads a driver • Driver talks to a particular database • An application can work with several databases by using all corresponding drivers • Ideal: can change database engines without changing any application code (not always in practice)

  24. JDBC Driver for Oracle • Download Oracle JDBC driver from oracle website given under “useful links” section of Oracle-JDBC notes page. • To install simply download and put ojdbc6.jar (or an appropriate driver for your database) in the class path

  25. Seven Steps • Load the driver • Define the connection URL • Establish the connection • Create a Statementobject • Execute a query using the Statement • Process the result • Close the connection

  26. Loading the Driver • We can register the driver indirectly using the statement Class.forName("oracle.jdbc.driver.OracleDriver"); • Class.forName loads the specified class • When OracleDriver is loaded, it automatically • creates an instance of itself • registers this instance with the DriverManager • Hence, the driver class can be given as an argument of the application

  27. An Example // A driver for imaginary1 Class.forName("ORG.img.imgSQL1.imaginary1Driver"); // A driver for imaginary2 Driver driver = new ORG.img.imgSQL2.imaginary2Driver(); DriverManager.registerDriver(driver); //A driver for MySQL Class.forName("com.mysql.jdbc.Driver"); imaginary1 MySQL imaginary2 Registered Drivers

  28. Connecting to the Database • Every database is identified by a URL • Given a URL, DriverManager looks for the driver that can talk to the corresponding database • DriverManager tries all registered drivers, until a suitable one is found

  29. Connecting to the Database Connection con = DriverManager. getConnection("jdbc:imaginaryDB1"); acceptsURL("jdbc:imaginaryDB1")? a r r imaginary1 Oracle imaginary2 Registered Drivers We Use:DriverManager.getConnection(<URL>, <user>, <pwd>);Where <UR>L is : jdbc:oracle:thin:@localhost:1521:xe

  30. Interaction with the Database • We use Statement objects in order to • Query the database • Update the database • Three different interfaces are used: Statement, PreparedStatement, CallableStatement • All are interfaces, hence cannot be instantiated • They are created by the Connection

  31. Querying with Statement String queryStr = "SELECT * FROM employee "+ "WHERE lname = ‘Wong'"; Statement stmt = con.createStatement(); ResultSet rs = stmt.executeQuery(queryStr); • The executeQuery method returns a ResultSet object representing the query result. • Will be discussed later…

  32. Changing DB with Statement String deleteStr = "DELETE FROM employee "+ "WHERE lname = ‘Wong'"; Statement stmt = con.createStatement(); int delnum = stmt.executeUpdate(deleteStr); • executeUpdate is used for data manipulation: insert, delete, update, create table, etc. (anything other than querying!) • executeUpdate returns the number of rows modified

  33. About Prepared Statements • Prepared Statements are used for queries that are executed many times • They are parsed (compiled) by the DBMS only once • Column values can be set after compilation • Instead of values, use ‘?’ • Hence, Prepared Statements can be though of as statements that contain placeholders to be substituted later with actual values

  34. Querying with PreparedStatement String queryStr = "SELECT * FROM employee "+ "WHERE superssn= ? and salary > ?"; PreparedStatement pstmt = con.prepareStatement(queryStr); pstmt.setString(1,"333445555"); pstmt.setInt(2, 26000); ResultSet rs = pstmt.executeQuery();

  35. Updating with PreparedStatement String deleteStr = “DELETE FROM employee "+ "WHERE superssn = ? and salary > ?"; PreparedStatement pstmt = con.prepareStatement(deleteStr); pstmt.setString(1, "333445555"); pstmt.setDouble(2, 26000); int delnum = pstmt.executeUpdate();

  36. Statements vs. PreparedStatements: Be Careful! • Are these the same? What do they do? String val ="abc"; PreparedStatement pstmt = con.prepareStatement("select * from R where A=?"); pstmt.setString(1, val); ResultSet rs = pstmt.executeQuery(); String val ="abc"; Statement stmt = con.createStatement( ); ResultSet rs = stmt.executeQuery("select * from R where A=" + val);

  37. Statements vs. PreparedStatements: Be Careful! • Will this work? • No!!! A ‘?’ can only be used to represent a column value PreparedStatement pstmt = con.prepareStatement("select * from ?"); pstmt.setString(1, myFavoriteTableString);

  38. Timeout • Use setQueryTimeOut(int seconds) of Statement to set a timeout for the driver to wait for a statement to be completed • If the operation is not completed in the given time, an SQLException is thrown • What is it good for?

  39. ResultSet • ResultSet objects provide access to the tables generated as results of executing a Statement queries • Only one ResultSet per Statement can be open at the same time! • The table rows are retrieved in sequence • A ResultSet maintains a cursor pointing to its current row • Thenext()method moves the cursor to the next row

  40. ResultSet Methods • boolean next() • activates the next row • the first call to next() activates the first row • returns false if there are no more rows • void close() • disposes of the ResultSet • allows you to re-use the Statement that created it • automatically called by most Statement methods

  41. ResultSet Methods • Type getType(int columnIndex) • returns the given field as the given type • indices start at 1 and not 0! • Type getType(String columnName) • same, but uses name of field • less efficient • For example: getString(columnIndex),getInt(columnName), getTime, getBoolean, getType,... • int findColumn(String columnName) • looks up column index given column name

  42. ResultSet Methods • JDBC 2.0 includes scrollable result sets. Additional methods included are : ‘first’, ‘last’, ‘previous’, and other methods.

  43. ResultSet Example Statement stmt = con.createStatement(); ResultSet rs = stmt. executeQuery("select lname,salary from employees"); // Print the result while(rs.next()) { System.out.print(rs.getString(1) + ":"); System.out.println(rs.getDouble(“salary")); }

  44. Mapping Java Types to SQL Types SQL type Java Type CHAR, VARCHAR, LONGVARCHAR String NUMERIC, DECIMAL java.math.BigDecimal BIT boolean TINYINT byte SMALLINT short INTEGER int BIGINT long REAL float FLOAT, DOUBLE double BINARY, VARBINARY, LONGVARBINARY byte[] DATE java.sql.Date TIME java.sql.Time TIMESTAMP java.sql.Timestamp

  45. Null Values • In SQL, NULL means the field is empty • Not the same as 0 or "" • In JDBC, you must explicitly ask if the last-read field was null • ResultSet.wasNull(column) • For example, getInt(column) will return 0 if the value is either 0 or NULL!

  46. Null Values • When inserting null values into placeholders of Prepared Statements: • Use the method setNull(index, Types.sqlType) for primitive types (e.g. INTEGER, REAL); • You may also use the setType(index, null) for object types (e.g. STRING, DATE).

  47. ResultSet Meta-Data A ResultSetMetaData is an object that can be used to get information about the properties of the columns in a ResultSet object An example: write the columns of the result set ResultSetMetaData rsmd = rs.getMetaData(); int numcols = rsmd.getColumnCount(); for (int i = 1 ; i <= numcols; i++) { System.out.print(rsmd.getColumnLabel(i)+" "); }

  48. Database Time • Times in SQL are notoriously non-standard • Java defines three classes to help • java.sql.Date • year, month, day • java.sql.Time • hours, minutes, seconds • java.sql.Timestamp • year, month, day, hours, minutes, seconds, nanoseconds • usually use this one

  49. Cleaning Up After Yourself • Remember to close the Connections, Statements, Prepared Statements and Result Sets con.close(); stmt.close(); pstmt.close(); rs.close()

  50. Dealing With Exceptions • An SQLException is actually a list of exceptions catch (SQLException e) { while (e != null) { System.out.println(e.getSQLState()); System.out.println(e.getMessage()); System.out.println(e.getErrorCode()); e = e.getNextException(); } }

More Related