What is SQL for? What does it look like? What are some fundamental features of SQL syntax? SQL is capable of complex operations but uses a simple and readable syntax.
- [Instructor] The purpose of this chapter is to give you a quick introduction to the basic elements of SQL. This will allow you to navigate the concepts and exercises in the rest of the course. Each of the lessons in this chapter will introduce you to a specific statement or concept in SQL. These lessons are not exhaustive and will skip over many essential details. Those details will be covered later in the course. SQL is designed for a specific task: to operate and manage a relational database. While capable of complex operations, SQL is a simple language with a simple syntax. A statement is the unitive execution in SQL. In this example, we have a SELECT statement that retrieves rows from the customer table, where the Continent column is equal to the value of a literal string, Europe. An SQL statement is terminated with a semicolon. The semicolon may not always be required in all contexts and all implementations, but it's usually a good idea to include it. An SQL statement often includes one or more clauses. This is a FROM clause. This specifies which table the SELECT statement will use. Likewise, the WHERE clause is used to specify which rows will be selected. The WHERE clause requires an expression. An expression evaluates to a value. Expressions are common in SQL and may be used with many clauses. This is a logical expression. All these parts together constitute a statement. A statement may be simple or it may be complex. We'll see many examples of statements throughout this course. The four fundamental functions of a database system are create, read, update, and delete, often abbreviated with the mnemonic CRUD. This chapter will cover the simplest forms of each of these essential operations. In standard SQL, the SELECT statement is used for all queries that return values. Some database engines have non-standard statements that may also retrieve information from the database, often metadata about the database itself. In standard SQL, the SELECT statement is how you get data from the database. It is the R in CRUD and often the most common statement you will use. In this chapter, we'll spend a bit of time with SELECT. We'll cover some of its most important features and options so you can easily select rows and columns from a table. The INSERT statement is used to add a row to a table. This is the C in CRUD. With INSERT, you may specify the table and the columns and the data that will be filled in those columns. Notice that this INSERT statement is separated on two lines. This is a common technique for improving readability. I have indented the second line to indicate that it's part of the same statement. The whitespace is ignored and the statement is terminated with a semicolon. The UPDATE statement is used to change data. This is the U in CRUD. The WHERE clause is used to select which row or rows are to be updated. Notice that I've used indenting to indicate that the assignment expressions are related to the SET clause. This is optional, but it's common and it improves readability. The DELETE statement is used to remove rows from a table. This is the D in CRUD. Again, the WHERE clause is used to select which row or rows are deleted. You'll see examples of these statements in the rest of this chapter, and we'll go into a lot more detail on each of these later in the course.
This course was created by Bill Weinman. We are pleased to offer this training in our library.
- How databases are organized
- Selecting rows and columns
- Creating new tables
- Inserting and updating data
- Sorting and filtering
- Accessing related tables with JOIN
- Working with strings
- Understanding numeric types
- Using aggregate functions and transactions
- Automating data with triggers
- Creating views
- Using CRUD functions