Before we dig into using a database, it's important to know what one is. Learn the basic terminology and how tables relate to real-world data.
- [Instructor] Before we start learning about a language used to talk to a database, we need to have an understanding of what a database is. At the most basic, a database is a collection of information. Think about a list of people, the city they live in, and their favorite color. We have three kinds of information, a name, a city, and a color. In a database, the individual kinds of information are organized into columns, and each set of information is organized into rows. Often, the columns are called fields and the rows are called records.
You could think of a record like a card with an individual person's information on it. The cards all have the same spaces for information, they have the same fields, but each card pertains to one person and each one is an individual record. Together, fields and records make up a table. With this kind of basic information in one table, we could use a spreadsheet like Excel to keep track of our data. But databases allow us not only to add more tables, but also to set up rules and relationships between the tables.
The layout and definition of how fields, tables, and relationships are set up is called the schema of the database. Creating very advanced setups with relationships and other parameters is beyond the scope of this course. But if you want to learn more about how databases work, be sure to check out Foundations of Programming: Databases. While this arrangement of data in a table can look like a spreadsheet, it becomes very difficult to ask some kinds of questions about data in a spreadsheet. With a spreadsheet, we can easily say what the sum total of a row or column is or sort information alphabetically.
But if we wanted to ask how many people in New York like the color purple, it gets a little bit more complicated. That's where databases become helpful, and that's why we have SQL.
- Name the predicate of the following statement: SELECT EyeColor, Age FROM Student WHERE FirstName = 'Tim' ORDER BY LastName ASC;
- Explain what to use to enforce the order in which an expression must be evaluated if the WHERE clause contains multiple expressions to evaluate.
- Identify the best option to join two tables in a database to be able to display data from both.
- List a data type that is not numeric.
- Determine the result of running the following statement on a table containing columns col_1 and col_2:
- INSERT INTO Box (col_1, col_2) VALUES ('A', 'B'), ('A', 'B'), ('A', 'B'), ('A', 'B');
- Determine the best approach of deleting Jon Ramirez (ID 3452) from a Student table.