1 / 18

SQL

SQL. SQL stands for S tructured Q uery L anguage SQL allows you to access a database SQL is an ANSI standard computer language SQL can execute queries against a database SQL can retrieve data from a database SQL can insert new records in a database

tal
Download Presentation

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. SQL SQL stands for Structured Query Language SQL allows you to access a database SQL is an ANSI standard computer language SQL can execute queries against a database SQL can retrieve data from a database SQL can insert new records in a database SQL can delete records from a database SQL can update records in a database

  2. History • Was designed and implemented by IBM Research (1986) • A joint with American National standards Institute (ANSI) and International Standards Organization (ISO) led to the standard version of SQL-86 • A revised and expanded in 1992 called SQL-92. • Most recent is now SQL-99

  3. Definitions and Data Types • Uses terms table, rows, columns for the formal relational model terms relations, tuple, and attribute. • An SQL schema is identified by a schema name, including authorization identifier to indicate user who owns it and descriptions for each element. • Schema elements include tables, constraints, views, domains, and other constructs • Catalog – a named collection of schemas in an SQL environment

  4. Common Data Types • char (size) – Fixed length character string. Size is specified in parenthesis. Max 255 bytes. • varchar (size) – Variable-length character string. Max size is specified in parenthesis. • number (size) – Number value with a max number of column digits specified in parenthesis. • date – Date value • number (size, d) – Number value with a max number of digits of “size” total, with a max number of “d” digits to the right of the decimal.

  5. Creating Tables • Create Table – used to specify a new relation by giving it a name, and attributes with initial constraints. • Example: CREATE TABLE company.employee … • Company is the schema name • Employee is the relation name

  6. Create table myemployees (firstname varchar(30), lastname varchar(30), title varchar (30), age number(2), salary number(8, 2)); Creates a table called myemployees First name, last name, and title allows 30 characters each. Age is allowed 2 digits Salary is allowed 8 digits with 2 decimals Creating a Table

  7. Other Functions • Select – allows you to select a certain and retrieve data specified. • Example: Select “column1” From “TableName” Where “condition”; • Select column 1 from the TableName with the following condition.

  8. Insert into “tablename” (first_column, … last_column) values (first_value,…last_value); Insert into employee (first, last, age, address, city, state) values (‘James’, ‘Tran’, 23, ‘1111 1st street’, ‘San Jose’, ‘California’); Inserts into specified table name Specify all columns inserting to separated by a comma. Values inserted are specified afterwards Strings enclosed in single quotes, numbers are not. Insertion

  9. update “tablename” set “columnname” = “newvalue” [,”nextcolumn” = “newvalue2”…] where “columnname” OPERATOR “value” [and | or “column” OPERATOR “value”]; **[ ] = optional Update the specified table name Set the column specified with new value Add in conditionals if needed Optional values and input add [ ] Updating

  10. delete from “tablename” where “columnname” OPERATOR “value” [and | or “column” OPERATOR “value”]; [ ] = optional Delete a certain table, column, row, etc. Operator meaning >,<,=, etc… Deleting

  11. Drop • Dropping a table removes all rows and definitions. • Example: Drop table “TableName”

  12. Ordering • SQL allows you to order elements in a table. • Example: orders by alphabetical select distinctcustomer-name fromborrower, loan whereborrower.loan-number = loan.loan-number and branch-name = ‘Perryridge’ order bycustomer-name

  13. Evaluation of GroupBy with Having

  14. Aggregates • More functions that allow you to operate on sets. • COUNT, SUM, AVG, MAX, MIN • Produces numbers, not tables. • Not part of relational algebra • Example: Select MAX (Age) From Employee E

  15. Grouping • Sometimes we want to apply aggregate functions to subgroups of tuples in a relation. • Such as finding the average salary of employees in each department or the number of employees that are working on each project. • SQL has a GROUP BY clause that allows for specific grouping of attributes

  16. Grouping Cont • Example: • SELECT Dno, COUNT(*), AVG (Salary) FROM EMPLOYEE GROUP BY Dno; • The EMPLOYEE tuples are partitioned into groups, each group having the same value for the grouping attribute Dno. • Then the COUNT and AVG functions are applied to each group.

  17. b) T T a) c) R S R(A) EXCEPT ALL S(A) d) T R(A) INTERSECT S(A) Two tables SQL Multiset Operations R(A) UNION ALL S(A)

  18. Nested Queries List all courses that were not taught in S2000 • SELECT C.CrsName • FROM Course C • WHERE C.CrsCode NOT IN • (SELECT T.CrsCode --subquery • FROM Teaching T • WHERE T.Sem = ‘S2000’) Evaluation strategy: subquery evaluated once to produces set of courses taught in S2000. Each row (as C) tested against this set.

More Related