Join Scott Simpson for an in-depth discussion in this video Use the exercise files, part of Learning SQL Programming.
- [Instructor] The exercise files for this course are available to anyone watching. results.db is the file I'll use here in the course, inside of the DB Browser for SQLite software. We'll use a SQLite database to minimize the setup and jump right into working with the SQL language. And I'll show you how to use the software in the next video. The file called quizresults.sql is a copy of the database for the course in a format that can imported into database management systems like MySQL or SQL Server in case you choose to use other software than the software I'm using to follow along with the course.
If you use that file, I'm depending on you or your administrator to know how to get the data into other software if you choose to take that route. Depending on the software you use, you'll need to open or import the file, and it'll create the database and the two tables that the course depends on. If you try to open quizresults.sql in DB Browser, you'll get an error. The database that the course uses includes two tables called People and States. The People table contains some information about a thousand people in the U.S. who participated in a fictional quiz competition.
We have their scores, what team they signed up to be part of, the company they work for, the city and state they live in, an ID number, and whether they opted to get a shirt or a hat for participating in the competition. The States table includes abbreviations and full names of states in the U.S. and information about what census region and division each state is part of. I generated the data in the People table using the website Mockaroo, which is a great way of generating different kinds of data for practicing with databases. And the state information is available from the U.S. Census website.
I've also included a file called statements.sql, which has a copy of every SQL statement used in the course organized by video. So, if you're typing along and have a problem, you can check what happened by comparing what you typed to what I typed. You'll be able to open this file in any plain text editor. And it's helpful to keep notes as you go along. If you're looking for a plain text editor, I recommend Visual Studio Code or Atom for Mac, Windows, or Linux. Or you can use the plain text editor mode of your system's text editor.
- 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.