SQL - Introduction and Basics

SQL (Structured Query Language) is a standard language for querying and manipulating data. It includes Data Definition Language (DDL) for creating, altering, and deleting tables, Data Manipulation Language (DML) for querying tables and modifying data, and more. Tables in SQL consist of attributes like Product, PName, Price, Category, and Manufacturer. Data types in SQL include atomic types like CHAR and INTEGER. Tuples in SQL represent records, and a table is a set of tuples. The basic form of a SQL query involves selecting attributes from relations based on specified conditions.

• SQL Basics
• Data Manipulation
• Database Querying
• Data Types

bednarczyk_a Follow

Uploaded on Sep 27, 2024 | 0 Views

Download Presentation

Please find below an Image/Link to download the presentation.

The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author. Download presentation by click this link. If you encounter any issues during the download, it is possible that the publisher has removed the file from their server.

Presentation Transcript

1. SQL

2. SQL Introduction Standard language for querying and manipulating data Structured Query Language Many standards out there: ANSI SQL, SQL92 (a.k.a. SQL2), SQL99 (a.k.a. SQL3), . Vendors support various subsets(e.g. PLSQL from Oracle)

3. SQL Data Definition Language (DDL) Create/alter/delete tables and their attributes Data Manipulation Language (DML) Query one or more tables discussed next ! Insert/delete/modify tuples in tables Connect to mysql on dbsrv2 mysql -h dbsrv2.cs.fsu.edu -u username -p

4. Tables in SQL Attribute names Table name Product PName Price Category Manufacturer Gizmo $19.99 Gadgets GizmoWorks Powergizmo $29.99 Gadgets GizmoWorks SingleTouch $149.99 Photography Canon MultiTouch $203.99 Household Hitachi Tuples or rows

5. Create a Tables CREATE TABLE table_name ( column_name1 data_type(size), column_name2 data_type(size), column_name3 data_type(size), .... );

6. Tables Explained The schema of a table is the table name and its attributes: Product(PName, Price, Category, Manfacturer) A key is an attribute whose values are unique; we underline a key Product(PName, Price, Category, Manfacturer)

7. Data Types in SQL Atomic types: Characters: CHAR(20), VARCHAR(50) Numbers: INTEGER(INT), FLOAT, DOUBLE, BIGINT Others: DATE, DATETIME, BLOB, Data types might have different names in different database!

8. Tables Explained A tuple = a record Restriction: all attributes are of atomic type A table = a set of tuples Like a list but it is unorderd: no first(), no next(), no last().

9. SQL QUERY Basic form: (plus many many more bells and whistles) SELECT <attributes> FROM <one or more relations> WHERE <conditions>

10. Simple SQL QUERY Product PName Price Category Manufacturer Gizmo $19.99 Gadgets GizmoWorks Powergizmo $29.99 Gadgets GizmoWorks SingleTouch $149.99 Photography Canon MultiTouch $203.99 Household Hitachi SELECT * FROM Product WHERE category= Gadgets PName Price Category Manufacturer Gizmo $19.99 Gadgets GizmoWorks Powergizmo $29.99 Gadgets GizmoWorks selection

11. Simple SQL QUERY Product PName Price Category Manufacturer Gizmo $19.99 Gadgets GizmoWorks Powergizmo $29.99 Gadgets GizmoWorks SingleTouch $149.99 Photography Canon MultiTouch $203.99 Household Hitachi SELECT PName, Price, Manufacturer FROM Product WHERE Price > 100 PName Price Manufacturer selection and projection SingleTouch $149.99 Canon MultiTouch $203.99 Hitachi

12. SQL Details Case insensitive: Same: SELECT Select select Same: Product product Different: Seattle seattle

13. The LIKE LIKE Operator SELECT * FROM Products WHERE PName LIKE %gizmo% LIKE p: pattern matching on strings p may contain two special symbols: % = any sequence of characters _ = any single character

14. Eliminating Duplicates Category SELECT DISTINCT category FROM Product Gadgets Photography Household Compare to: Category Gadgets SELECT category FROM Product Gadgets Photography Household

15. Ordering the Results SELECT pname, price, manufacturer FROM Product WHERE category= gizmo AND price > 50 ORDER BY price, pname Ties are broken by the second attribute on the ORDER BY list, etc. Ordering is ascending, unless you specify the DESC keyword.

16. Keys and Foreign Keys Company CName StockPrice Country GizmoWorks 25 USA Key Canon 65 Japan Hitachi 15 Japan Product PName Gizmo Powergizmo SingleTouch MultiTouch Price $19.99 $29.99 $149.99 $203.99 Category Gadgets Gadgets Photography Household Manufacturer GizmoWorks GizmoWorks Canon Hitachi Foreign key

17. Aggregation SELECT avg(price) FROM Product WHERE maker= Toyota SELECT count(*) FROM Product WHERE year > 1995 SQL supports several aggregation operations: sum, count, min, max, avg Except count, all aggregations apply to a single attribute

18. Aggregation : Count COUNT applies to duplicates, unless otherwise stated: same as Count(*) SELECT Count(category) FROM Product WHERE year > 1995 We probably want: SELECT Count(DISTINCT category) FROM Product WHERE year > 1995

19. More Examples Purchase(product, date, price, quantity) SELECT Sum(price * quantity) FROM Purchase What do they mean ? SELECT Sum(price * quantity) FROM Purchase WHERE product = bagel

20. Simple Aggregation Purchase Product Bagel Banana Banana Bagel Date 10/21 10/3 10/10 10/25 Price 1 0.5 1 1.50 Quantity 20 10 10 20 SELECT Sum(price * quantity) FROM Purchase WHERE product = bagel 50 (= 20+30)

21. Modifying the Database Three kinds of modifications Insertions Deletions Updates Sometimes they are all called updates

22. Insertions General form: INSERT INTO R(A1, ., An) VALUES (v1, ., vn) Example: Insert a new purchase to the database: INSERT INTO Purchase(buyer, seller, product, store) VALUES ( Joe , Fred , wakeup-clock-espresso-machine , The Sharper Image ) Missing attribute NULL. May drop attribute names if give them in order.

23. Insertions INSERT INTO PRODUCT(name) SELECT DISTINCT Purchase.product FROM Purchase WHERE Purchase.date > 10/26/01 The query replaces the VALUES keyword. Here we insert many tuples into PRODUCT

24. Insertion: an Example Product(name, listPrice, category) Purchase(prodName, buyerName, price) prodName is foreign key in Product.name Suppose database got corrupted and we need to fix it: Purchase Product prodName buyerName price name listPrice category camera John 200 gizmo Smith 80 gizmo 100 gadgets camera Smith 225 Task: insert in Product all prodNames from Purchase

25. Insertion: an Example INSERT INTO Product(name) SELECT DISTINCT prodName FROM Purchase WHERE prodName NOT IN (SELECT name FROM Product) name listPrice category gizmo 100 Gadgets camera - -

26. Insertion: an Example INSERT INTO Product(name, listPrice) SELECT DISTINCT prodName, price FROM Purchase WHERE prodName NOT IN (SELECT name FROM Product) name listPrice category gizmo 100 Gadgets camera 200 - Depends on the implementation camera ?? 225 ?? -

27. Deletions Example: DELETE FROM PURCHASE WHERE seller = Joe AND product = Brooklyn Bridge Factoid about SQL: there is no way to delete only a single occurrence of a tuple that appears twice in a relation.

28. Updates Example: UPDATE PRODUCT SET price = price/2 WHERE Product.name IN (SELECT product FROM Purchase WHERE Date = Oct, 25, 1999 );

29. Alter-Add ALTER TABLE table_name ADD column_name datatype

30. Alter- Drop ALTER TABLE table_name DROP column_name