Database Fundamentals/Selecting Data

This lesson introduces selecting data.

Objectives and Skills

Objectives and skills for the selecting data portion of Microsoft Exam 98-364 Database Fundamentals include:^[1]

• Manipulate data
  • Select data
    • Utilize SELECT queries to extract data from one table, extract data by using joins, combine result sets by using UNION and INTERSECT

Readings

1. Read Wikipedia: Select (SQL).

Multimedia

1. Watch Microsoft Virtual Academy: Using DML Statements.
2. Watch YouTube: Introducing Query Design.
3. Watch YouTube: SQL Server Queries Part 1 - Writing Basic Queries.
4. Watch YouTube: SQL JOIN Statement.
5. Watch YouTube: SQL GROUP BY, HAVING & ORDER BY.

Activities

1. Use SQL Server to list items from the Student and Course tables in your College database.
2. Write the following queries:
   • Watch YouTube: SELECT Statement.
   • Watch YouTube: Introducing Query Designer.
   • Read Microsoft: SQL Server SELECT - SQL Command.
   • Read Microsoft: SELECT Examples (Transact - SQL).
   • Read W3Schools.com: SQL SELECT Statement.
   • Write a query that displays all of the columns and all of the rows from the Course table to display the CourseID, Description, CreditHours fields.
   • Write a query that will display the name of all students from the Student table.
   • Modify the previous query to display only students with your last name.
   • Write a query that will return one column name CourseID, Description and contains the last name of the instructor.
   • Practice creating simple queries using the College database you created.

Lesson Summary

• A SELECT statement retrieves zero or more rows from one or more database tables or database views.^[2]
• A SQL join clause combines records from two or more tables in a relational database. A JOIN is a means for combining fields from two tables (or more) by using values common to each. .^[3]
• A CROSS JOIN will produce rows which combine each row from the first table with each row from the second table.^[4]
• A INTERSECT clause combines the results of two queries and returns only rows that appear in both result sets. For purposes of duplicate removal the INTERSECT operator does not distinguish between NULLs. The INTERSECT operator removes duplicate rows from the final result set.^[5]
• A UNION clause combines the results of two SQL queries into a single table of all matching rows. The two queries must result in the same number of columns and compatible data types in order to unite.^[6]

Key Terms

cross join

intersect

join

select

union

See Also

• Microsoft: Data Manipulation Language (DML) Statements (Transact-SQL)
• Microsoft: Comparison Operators (Transact-SQL)
• Microsoft: Using Joins
• W3Schools.com: SQL SELECT Statement

References

Resource type: this resource is a lesson.