1 / 37

Information Technologies and Microsoft SQL Server

Information Technologies and Microsoft SQL Server. Day 2 by Alper Özpınar ozpinar@zetabt.com. Parts of a database. Attributes (fields) An attribute or field is a component of a record that describes something about an item. Records A record is the representation of an individual item.

stefan
Download Presentation

Information Technologies and Microsoft SQL Server

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. Information Technologies and Microsoft SQL Server Day 2 by Alper Özpınar ozpinar@zetabt.com

  2. Parts of a database • Attributes (fields) • An attribute or field is a component of a record that describes something about an item. • Records • A record is the representation of an individual item. • Table • A collection of records • Database • A collection of tables and rules for accessing the tables

  3. What is a relational database? • Originally developed by E.F. Codd in 1970 • Organizes data into tables where each item is a row and the attributes of the item are in columns. • Different from “flat file” databases because you can define “relationships” between items in different tables.

  4. Parts of a database Record Tables Attribute/Field • Records become “rows” • Attributes/fields become “columns” • Rules determine the relationship between the tables and tie the data together to form a database

  5. Kinds of Relationships • “One to One” • One row of a table matches exactly to another • One person, one id number, one address • “One to Many” • One row of a table matches many of another • One person, many phone numbers • “Many to Many” • One row may match many of another or many rows match one row of another

  6. Creating a database • What information are we trying to store? • How do we describe the information? • Phone Book/Contact entries • Name • Address • Company • Phone Number • URL/Web Page • Age • Height (in meters) • Birthday • When we added the entry

  7. Data Types • Binary • Database specific binary objects • Pictures, digital signatures, etc. • Boolean • True/False values • Character • Fixed width or variable size • Numeric • Integer, Real (floating decimal point), Money • Temporal • Time, Date, Timestamp

  8. Phone Book/Contact Record Name Character Address Character Company Character Phone Number Character URL/Web Page Character Age Integer Height Real (float) Birthday Date When we added the entry Timestamp

  9. Basic SQL Commands • Creating tables with CREATE • Adding data with INSERT • Viewing data with SELECT • Removing data with DELETE • Modifying data with UPDATE • Destroying tables with DROP

  10. SQL Select • SELECT "column_name" FROM "table_name“ • SELECT * FROM customers • SELECT name,surname FROM customers

  11. SQL Select & Where • SELECT "column_name"FROM "table_name"WHERE "condition“ • SELECT *FROM employeeWHERE salary>1000

  12. SQL Select & Where & Conditions • SELECT "column_name"FROM "table_name"WHERE "simple condition"{[AND|OR] "simple condition"}+ • SELECT store_nameFROM Store_InformationWHERE Sales > 1000OR (Sales < 500 AND Sales > 275)

  13. And & Or • Logical Operations • 1 and 1 =1 • 1 and 0 =0 • 0 and 1 =0 • 0 and 0 =0 • 1 or 1 = 1 • 0 or 1 = 1 • 1 or 0 = 1 • 0 or 0 = 0

  14. SQL Select & IN • SELECT "column_name"FROM "table_name"WHERE "column_name" IN ('value1', 'value2', ...) • The number of values in the parenthesis can be one or more, with each values separated by comma. Values can be numerical or characters. If there is only one value inside the parenthesis, this commend is equivalent to • WHERE "column_name" = 'value1'

  15. SQL Between • SELECT "column_name"FROM "table_name"WHERE "column_name" BETWEEN 'value1' AND 'value2‘ • SELECT *FROM Store_InformationWHERE Date BETWEEN 'Jan-06-1999' AND 'Jan-10-1999'

  16. SQL Like • LIKE allows you to do a search based on a pattern rather than specifying exactly what is desired (as in IN) or spell out a range (as in BETWEEN). The syntax for is as follows: • SELECT "column_name"FROM "table_name"WHERE "column_name" LIKE {PATTERN} • SELECT *FROM Store_InformationWHERE store_name LIKE '%AN%'

  17. SQL Like • 'A_Z': All string that starts with 'A', another character, and end with 'Z'. For example, 'ABZ' and 'A2Z' would both satisfy the condition, while 'AKKZ' would not (because there are two characters between A and Z instead of one). • 'ABC%': All strings that start with 'ABC'. For example, 'ABCD' and 'ABCABC' would both satisfy the condition. • '%XYZ': All strings that end with 'XYZ'. For example, 'WXYZ' and 'ZZXYZ' would both satisfy the condition. • '%AN%': All string that contain the pattern 'AN' anywhere. For example, 'LOS ANGELES' and 'SAN FRANCISCO' would both satisfy the condition.

  18. SQL Order By • SELECT "column_name"FROM "table_name"[WHERE "condition"]ORDER BY "column_name" [ASC, DESC] • For more columns • ORDER BY "column_name1" [ASC, DESC], "column_name2" [ASC, DESC] • SELECT store_name, Sales, DateFROM Store_InformationORDER BY Sales DESC

  19. SQL Aggregate Functions • AVG • COUNT • MAX • MIN • SUM • The syntax for using functions is, • SELECT "function type"("column_name")FROM "table_name" • SELECT SUM(Sales) FROM Store_Information

  20. SQL Count • SELECT COUNT("column_name")FROM "table_name" • Select Count(*) from Customers • Counts the number of rows in table

  21. Count Distinct • COUNT and DISTINCT can be used together in a statement to fetch the number of distinct entries in a table. • SELECT COUNT(DISTINCT store_name)FROM Store_Information

  22. SQL Group By • SELECT "column_name1", SUM("column_name2")FROM "table_name"GROUP BY "column_name1“ • SELECT store_name, SUM(Sales)FROM Store_InformationGROUP BY store_name

  23. SQL Having • Instead of using the WHERE clause in the SQL statement, though, we need to use the HAVING clause, which is reserved for aggregate functions. The HAVING clause is typically placed near the end of the SQL statement, and a SQL statement with the HAVING clause may or may not include the GROUP BY clause. The syntax for HAVING is, • SELECT "column_name1", SUM("column_name2")FROM "table_name"GROUP BY "column_name1"HAVING (arithmetic function condition)

  24. SQL Having • SELECT store_name, SUM(sales)FROM Store_InformationGROUP BY store_nameHAVING SUM(sales) > 1500

  25. SQL Alias • SELECT "table_alias"."column_name1" "column_alias"FROM "table_name" "table_alias" • Two reason to use • Change column name • Take data from two table or table alias • SELECT A1.store_name Store, SUM(A1.Sales) "Total Sales"FROM Store_Information A1GROUP BY A1.store_name

  26. Joining Tables • Used for combining data from different tables

  27. SQL Join • With tables named A1 and A2 • SELECT A1.region_name REGION, SUM(A2.Sales) SALESFROM Geography A1, Store_Information A2WHERE A1.store_name = A2.store_nameGROUP BY A1.region_name

  28. Different types of JOINs • “Inner Join” • Unmatched rows in either table aren’t printed • “Left Outer Join” • All records from the “left” side are printed • “Right Outer Join” • All records from the “right” side are printed • “Full Outer Join” • All records are printed • Multiple Table Join • Join records from multiple tables

  29. SQL Union • The purpose of the SQL UNION command is to combine the results of two queries together. In this respect, UNION is somewhat similar to JOIN in that they are both used to related information from multiple tables. One restriction of UNION is that all corresponding columns need to be of the same data type. Also, when using UNION, only distinct values are selected (similar to SELECT DISTINCT).

  30. SQL Union • [SQL Statement 1]UNION[SQL Statement 2] • we want to find out all the dates where there is a sales transaction.

  31. SQL Union • SELECT Date FROM Store_InformationUNIONSELECT Date FROM Internet_Sales • Result:

  32. SQL Union ALL • The purpose of the SQL UNION ALL command is also to combine the results of two queries together. The difference between UNION ALL and UNION is that, while UNION only selects distinct values, UNION ALL selects all values. • The syntax for UNION ALL is as follows: • [SQL Statement 1]UNION ALL[SQL Statement 2]

  33. SQL Union All SELECT Date FROM Store_InformationUNION ALLSELECT Date FROM Internet_Sales

  34. Record Operations

  35. SQL Insert • INSERT INTO table_name (col_name1, … col_namen) VALUES (value1, …, valuen) • INSERT INTO Corvettes(Vette_id, Body_style, Miles, Year, State) VALUES (37, 'convertible', 25.5, 1986, 17)

  36. SQL Update • To change one or more values of a row in a table UPDATE table_name SET col_name1 = value1, … col_namen = valuen WHERE col_name = value • The WHERE clause is the primary key of the row to be updated • UPDATE Corvettes SET Year = 1996 WHERE Vette_id = 17

  37. SQL Delete • Delete has the following syntax: delete rel-name where qualification • Example: Fire all those sailors whose rating is less than 2. delete sailors where rating < 2

More Related