Sql Chapter Two - презентация, доклад, проект скачать

Нажмите для полного просмотра!

Содержание ▲

Вы можете ознакомиться и скачать презентацию на тему Sql Chapter Two. Доклад-сообщение содержит 29 слайдов. Презентации для любого класса можно скачать бесплатно. Если материал и наш сайт презентаций Mypresentation Вам понравились – поделитесь им с друзьями с помощью социальных кнопок и добавьте в закладки в своем браузере.

Слайды и текст этой презентации

Слайд 1

Описание слайда:

SQL Chapter Two

Слайд 2

Описание слайда:

Overview Basic Structure Verifying Statements Specifying Columns Specifying Rows

Слайд 3

Описание слайда:

Introduction SQL is a modular language that uses statements and clauses.

Слайд 4

Описание слайда:

Basic structure of PROC SQL: PROC SQL; statement (select) clauses (from, where, group by, having, order by); QUIT; Note: place semicolon at the end of the last clause only.

Слайд 5

Описание слайда:

Statements select - specifies the columns to be selected Select statement has the following features: -selects data that meets certain conditions -groups data -specifies an order for the data -formats data -calculates new variables

Слайд 6

Описание слайда:

Clauses from - specifies the tables to be queried where - subsets the data based on a condition - optional group by - classifies the data into groups - optional having - subsets groups of data based on a group condition order by - sorts row by the values of specific columns Note: the order of the clauses are significant.

Слайд 7

Описание слайда:

Overview Basic Structure Verifying Statements Specifying Columns Specifying Rows

Слайд 8

Описание слайда:

Verifying Statements Two functions that can be used to verify if your statement syntax are: validate - used to check the select statement syntax noexec - checks for invalid syntax in all types of SQL statements

Слайд 9

Описание слайда:

Validate proc sql; validate select timemile, restpulse, maxpulse from project.fitness where timemile gt 7; NOTE: PROC SQL statement has valid syntax.

Слайд 10

Описание слайда:

NoExect proc sql noexec; select timemile, restpulse, maxpulse from project.fitness where timemile gt 7; NOTE: Statement not executed due to NOEXEC option.

Слайд 11

Описание слайда:

Contrasting Features of validate: -tests syntax of query without executing the query -checks the validity of column name -prints error messages for invalid queries -is only used for select statements

Слайд 12

Описание слайда:

Overview Basic Structure Verifying Statements Specifying Columns Specifying Rows

Слайд 13

Описание слайда:

Specifying Columns Objectives -Displaying columns directly from a table -Displaying columns calculated from other columns -Calculating columns using a CASE expression

Слайд 14

Описание слайда:

Displaying data from a table To print all of a table columns in the order that they were stored, use an asterisk in the SELECT statement: PROC SQL; SELECT * FROM VITALS; QUIT;

Слайд 15

Описание слайда:

Printing Specify Columns If you do not want to print out all columns in a table in the order that they were stored, you can specify the columns to be printed in the order that you want them in the SELECT statement or CASE EXPRESSION in the select statement . PROC SQL; CREATE TABLE TESTMED AS SELECT PATIENT, CASE ((PATIENT/2 = INT(PATIENT/2)) + (PATIENT = .)) WHEN 1 THEN 'Med A' WHEN 0 THEN 'Med B' ELSE 'Error' END AS DOSEGRP LENGTH=5 FROM VITALS ORDER BY PATIENT; QUIT;

Слайд 16

Описание слайда:

Calculating Columns We can calculate a new column by using data in an existing column and then naming the new column using the as function. Calculate the proportion of Units form each country CODE:

Слайд 17

Описание слайда:

Calculated columns using SAS Dates Recall from previous chapters in our SAS book that dates are stored in a different format when run through SAS. We will then use these dates to calculate new columns.

Слайд 18

Описание слайда:

Example: Calculate the range of dates in a Dailyprices dataset. CODE:

Слайд 19

Описание слайда:

Creating new columns The use of CASE expression can be used to create a new column CODE:

Слайд 20

Описание слайда:

Creating a table To create and populate a table with the rows from an SQL query, use create table. proc sql; create table states as select state_code, state_name from d2data.state; quit;

Слайд 21

Описание слайда:

Overview Basic Structure Verifying Statements Specifying Columns Specifying Rows

Слайд 22

Описание слайда:

Specifying Rows in a table Objectives -Selecting a subset of rows -Removing duplicate rows -Subsetting using where clauses, escape clauses, and calculated values

Слайд 23

Описание слайда:

Selecting a subset of rows proc sql; title 'large orders'; select Product_ID, total_retail_price from d2data.order_item where total_retail_price > 1000; quit;

Слайд 24

Описание слайда:

Where clause Use a where to specify a condition that data must fulfill before being selected. CODE: OUTPUT: Where clauses uses common comparisons (lt, gt, eq, etc) and logical operators (OR, Not, And, In, Is Null, ...).

Слайд 25

Описание слайда:

Removing duplications Use distinct keyword to eliminate duplications. CODE (without DISTINCT): CODE (with DISTINCT): OUTPUT:

Слайд 26

Описание слайда:

Escape Clause The escape clause allows you to designate a single character that will indicate how proc sql will interpret LIKE wildcards when SAS is searching within a character string. CODE:

Слайд 27

Описание слайда:

Subsetting calculated values Since the where clause is evaluated before the select, it's possible for an error to show up since the columns used in the where clause must exist in the table or be derived from an existing column. There are two fixes for this, the first would be repeating the calculation in the where clause. The alternative method would be using CALCULATED keyword to refer to an already calculated column in the select.

Слайд 28

Описание слайда:

Subsetting calculated values proc sql; title 'Lack of profit'; select Product_ID, ((total_retail_price/quantity) - costprice_per_Unit) as profit from d2data.order_item where calculated profit < 3; quit; title;

Слайд 29

Описание слайда:

Summary Basic Structure PROC SQL; statement (select) clauses (from, where, group by, having, order by); QUIT; Verifying Statements validate - used to check the select statement syntax noexec - checks for invalid syntax in all types of SQL statements Specifying Columns Displaying columns directly from a table Displaying columns calculated from other columns Calculating columns using a CASE expression Specifying Rows Selecting a subset of rows Removing duplicate rows Subsetting using where clauses, escape clauses, and calculated values

Теги Chapter

Похожие презентации