252 Part I EXAM PREPARATION TIPJoins and Derived
252 Part I EXAM PREPARATION TIPJoins and Derived Tables Joins and derived tables will figure prominently in your exam. Joins are the backbone of relational databases; they actually put the relation in relational databases. They re used in all the main SQL statements (SELECT, INSERT, UPDATE, and DELETE). They re very important. Also, derived tables tend to be overlooked, and they re perceived as complicated, even though they re not, so they re also likely to show up. EXAM RETRIEVE AND FILTER DATA USING TRANSACT-SQL . Retrieve and filter data using Transact-SQL. SQL Server is a relational database management system. This section focuses on the Relational part. It covers how tables relate to one another. In Chapter 2, you covered how Primary Keys and Foreign Keys are used in a database model, and how they impact storing data. This chapter shows you how to retrieve data from a relational database. This involves using the various join types: INNER, RIGHT, LEFT, and CROSS. You re also going to learn about using a technique called derived tables to simplify query writing. A common data model is used throughout this chapter for the examples. This is to make life a bit easier for you. Here are the table layouts and some sample data: CREATE TABLE Person ( PersonID int IDENTITY(1,1) NOT NULL, FirstName varchar(50) NULL, LastName varchar(50) NOT NULL, ) CREATE TABLE PersonAddress( PersonID int NOT NULL, AddressID int NOT NULL ) CREATE TABLE Address ( AddressID int IDENTITY(1,1) NOT NULL, StreetAddress varchar(250) NOT NULL, City varchar(50) NOT NULL, State varchar(50) NOT NULL, ZipCode char(5) NULL ) CREATE TABLE Sales ( PersonID int, ProductID int, QtyPurchased int, DatePurchased datetime ) CREATE TABLE Product ( ProductID int NOT NULL, ProductDescription varchar(15) NOT NULL ) This model is for a many-to-many relationship between the Person table and the Address table. This means that one person can have several addresses, and several people can live at the same address. In
For reliable and cheap web hosting services please check cheap web hosting website.