Basic SQL and Single Table Queries Week 1

Basic SQL and Single Table Queries Week 1 - Basic SQL and...

Info iconThis preview shows pages 1–2. Sign up to view the full content.

View Full Document Right Arrow Icon
Basic SQL and Single Table Queries Review of ER Diagrams | What is SQL? | A Basic SELECT Statement | Restricting Rows | Wildcards | Null Values | Logical Operators | Transforming Strings | Calculated Fields | Tutorials This week we will begin looking at how to extract data from our databases by listing data from a single table using SQL SELECT statements. A SELECT statement is made up of a number of clauses that allow us to specify the fields to be listed in the output, the table those fields come from, conditions to restrict the rows returned from the table, and how we want the results to be sorted. But before we can begin writing SELECT statements, we have to be familiar with the database so that we know what tables contain the data needed to produce the required results. Review of ER Diagrams In BIS245 you learned how to design efficient and effective databases. You used Entity Relationship diagrams to provide a visual representation of your database. You will remember that an Entity maps to a Table in the database and a Relationship defines how tables are related to each other. This is important to understand when we are getting information out of multiple tables. There are three types of relationships: 1:1: This is a one-to-one relationship, in which each record in the table (or relation) is associated with one, and only one, record in another table. 1:N: This is a one-to-many relationship, in which a single record on one side of the relationship will have zero or more records on the many side of the relationship. For example, a single customer will place many orders. N:M: This is a many-to-many relationship, in which each record on both sides of the relationship can be associated with many records on the other side of the relationship. For example, a Student can belong to many Clubs, and a Club will have many Students. (Note that most modern DBMSs do not directly support many-to-many relationships; many-to-many relationships are implemented using a bridging table.) When you begin working with a database that you were not involved in designing, it takes time to get to know the database. An ER diagram is a good place to begin! Below is a diagram of the coffee merchant database that we'll use for many examples later in this lecture. You should look it over to become familiar with its tables, columns (fields), and relationships: What is SQL? SQL stands for Structured Query Language and it is the language of relational databases. It is used to manage every aspect of the database, including creating the database and the tables, adding data to the tables, updating the data, and selecting data in response to a query. SQL is an ANSI (American National Standards Institute) standard language, which means that all database vendors implementing SQL must adhere to specific requirements, making it almost a universal language. But that does not stop vendors from also implementing their own extensions to SQL. So while database vendors such as Microsoft Access, Microsoft SQL Server, Oracle, DB2 and mySQL all support
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Image of page 2
This is the end of the preview. Sign up to access the rest of the document.

Page1 / 6

Basic SQL and Single Table Queries Week 1 - Basic SQL and...

This preview shows document pages 1 - 2. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online