01_manage data with.pptx - SQ DATABASE Fundamental 12...

This preview shows page 1 - 11 out of 57 pages.

© Copyright 2017 SQ DATABASE Fundamental 12 February 2018
Image of page 1

Subscribe to view the full document.

© Copyright 2017 Module 1 : Manage data with Transact-SQL Courses Module Skill 1.1: Create Transact-SQL SELECT queries Skill 1.2: Query multiple tables by using joins Skill 1.3: Implement functions and aggregate data Skill 1.4: Modify data
Image of page 2
© Copyright 2017 Manage data with Transact-SQL
Image of page 3

Subscribe to view the full document.

© Copyright 2017 Transact-SQL (T-SQL) is the main language used to manage and manipulate data in Microsoft SQL Server and Azure SQL Database. Skills in this chapter: Create Transact-SQL SELECT queries Query multiple tables by using joins Implement functions and aggregate data Modify data
Image of page 4
© Copyright 2017 Skill 1.1: Create Transact-SQL SELECT queries Understanding logical query processing The main statement used to retrieve data in T-SQL is the SELECT statement. Following are the main query clauses specified in the order that you are supposed to type them (known as “keyed-in order”): 1. SELECT 2. FROM 3. WHERE 4. GROUP BY 5. HAVING 6. ORDER BY
Image of page 5

Subscribe to view the full document.

© Copyright 2017 SELECT country , YEAR ( hiredate ) AS yearhired , COUNT (*) AS numemployees FROM MS_GEN_Employee WHERE hiredate > '2010' GROUP BY country , YEAR ( hiredate ) HAVING COUNT (*) > 1 ORDER BY country , yearhired DESC ;
Image of page 6
© Copyright 2017 Getting started with the SELECT statement The SELECT clause The SELECT clause of a query has two main roles: It evaluates expressions that defie the attributes in the query’s result, assigning them with aliases if needed. Using a DISTINCT clause, you can eliminate duplicate rows in the result if needed. Let’s start with the fist role. Take the following query as an example: SELECT empid, firstname, lastname FROM HR.Employees;
Image of page 7

Subscribe to view the full document.

© Copyright 2017 Filtering data with predicates Predicates and three-valued-logic SELECT empid, firstname, lastname, country, region, city FROM HR.Employees; This query generates the following output: empid firstname lastname country region city ----- ---------- ---------- -------- ------- --------- 1 Sara Davis USA WA Seattle 2 Don Funk USA WA Tacoma 3 Judy Lew USA WA Kirkland 4 Yael Peled USA WA Redmond 5 Sven Mortensen UK NULL London 6 Paul Suurs UK NULL London 7 Russell King UK NULL London 8 Maria Cameron USA WA Seattle 9 Patricia Doyle UK NULL London
Image of page 8
© Copyright 2017 Filtering data with predicates Predicates and three-valued-logic SELECT empid, firstname, lastname, country, region, city FROM HR.Employees WHERE region <> N'WA'; The predicate evaluates to false for rows with WA in the region attribute and those rows are discarded. The predicate would have evaluated to true had there been rows with a present region other than WA, and those rows would have been returned. Combining predicates WHERE col1 = 'w' AND col2 = 'x' OR col3 = 'y' AND col4 = 'z'
Image of page 9

Subscribe to view the full document.

© Copyright 2017 Filtering data with predicates Filtering character data A classic example for using incorrect literal types is with Unicode character strings (NVARCHAR and NCHAR types).
Image of page 10
Image of page 11
  • Spring '16
  • Antin
  • Microsoft SQL Server, Join, shipcountry NVARCHAR

{[ snackBarMessage ]}

Get FREE access by uploading your study materials

Upload your study materials now and get free access to over 25 million documents.

Upload now for FREE access Or pay now for instant access
Christopher Reinemann
"Before using Course Hero my grade was at 78%. By the end of the semester my grade was at 90%. I could not have done it without all the class material I found."
— Christopher R., University of Rhode Island '15, Course Hero Intern

Ask a question for free

What students are saying

  • Left Quote Icon

    As a current student on this bumpy collegiate pathway, I stumbled upon Course Hero, where I can find study resources for nearly all my courses, get online help from tutors 24/7, and even share my old projects, papers, and lecture notes with other students.

    Student Picture

    Kiran Temple University Fox School of Business ‘17, Course Hero Intern

  • Left Quote Icon

    I cannot even describe how much Course Hero helped me this summer. It’s truly become something I can always rely on and help me. In the end, I was not only able to survive summer classes, but I was able to thrive thanks to Course Hero.

    Student Picture

    Dana University of Pennsylvania ‘17, Course Hero Intern

  • Left Quote Icon

    The ability to access any university’s resources through Course Hero proved invaluable in my case. I was behind on Tulane coursework and actually used UCLA’s materials to help me move forward and get everything together on time.

    Student Picture

    Jill Tulane University ‘16, Course Hero Intern