Join Bill Weinman for an in-depth discussion in this video What is SQL?, part of SQL Essential Training.
SQL stands for Structured Query Language. It's used for creating, querying, updating and manipulating modern relational databases. SQL is used on virtually all major platforms, and by virtually all major relational database systems. There are some modern database systems that are not relational and do not use SQL. But those are specialized systems. And they're commonly used along with relational databases, and very rarely instead of. Some people try to pronounce SQL as if it had vowels like sequel.
This is technically incorrect. In fact the original ANSI standard declared that the correct pronunciation is SQL. Many people say sequel, and it's certainly okay if you choose to pronounce it that way. I prefer to say SQL. The SQL language can be divided into two major categories. The Data Definition Language, or DDL, is used to define tables, indexes, and relationships. And the Data Manipulation Language, or DML, is used to add, query, manipulate, and delete data from tables and datasets.
In order to communicate clearly, we need to agree on some terminology. Many terms, especially in the computer world, may mean different things in different contexts or even just among different people. Here are some terms that I'll use in this course and what they mean in this context. SQL is specifically designed to work with relational databases. A relational database is a data store that is capable of representing relationships between different sets of data. For example, a database may have a table of customers and a table of addresses.
A single customer may have more than one address and a single address may have more than one customer. A relational database has facilities for describing these relationships and SQL was designed to make these features accessible and flexible. In a relational database information is said to be presented in rows and columns. A row of data is analogous to a single data record. In fact, those of us who have been using databases since before SQL, sometimes still refer to rows as records.
You may want to think of a row as a logical set of data items. But this is not always accurate. It's also common to query data in columns. I tend to use the term, record, for a query result that may not translate directly into a table row. This seems like a picky distinction, and maybe it is. I tend to think of things in pretty specific terms. For example, a table may be organized to hold one musical album per row. Each row would have the album ID, title, artist, label, and release date.
A column is typically a set of objects of the same kind and usage. They're commonly of the same data type, but in modern usage that's not always a safe assumption. Columns span rows. Using our example, the customer table may have columns for customer ID, customer name, customer email address and primary contact. A table is a collection of rows and columns. So this example is an album table. It contains columns that describe the types of information stored and the rows that represent each album.
A database is a collection of tables, and often other metadata like indexes, triggers, and stored procedures. Each database system will have its own format for what metadata gets stored in the database. A DBMS is a database management system, like Oracle, PostgreSQL, SQLite, MySQL, or Microsoft SQL Server. There are two major contexts where you will see SQL used. SQL is often used to interact directly with the database often using a vendor supplied interface.
SQL is also commonly embedded in another programming language, allowing the program to manipulate and query the database according to the algorithms of the program itself. For example, a customer management program, probably uses SQL to communicate with the database. The programmers who created the program put SQL statements in the code, along with the programming language used to create the logic and flow of the application. You'll see an example of this later in the course when I show you the CRUD application. In this course I'll be covering standard SQL, such as it is.
It's the nature of database management systems that there is a tremendous divergence between how SQL is implemented in each system. Every major database management system has its own set of features. Its own legacy, its own history, its own Zen of how things are done. And alas, its own flavor of SQL. After learning standard SQL, you'll be able to use any of these systems, but you will still need to learn the particular implementation of SQL for the system you are using.
- Understanding SQL terminology and syntax
- Creating new tables and records
- Inserting and updating data
- Writing basic SQL queries
- Sorting and filtering
- Accessing related tables with JOIN
- Working with strings
- Finding the numeric type of a value
- Using aggregate functions and transactions
- Updating a table with triggers
- Creating views
Skill Level Beginner
Q: For Mac OS X: When I try to start the Apache Web Server from the XAMPP control panel, it doesn't start, and when I open "localhost" in my web browser, I see a white screen that says "It Works!" instead of the XAMPP page.
sudo apachectl stop
Q: I'm on a Mac, and I get an error in SID that says "attempt to write a read only database." How can I fix this?
A: This usually means that the database folder does not have sufficient permissions for writing by the web user. This can happen if you create the SQL folder new, rather than copying it from the Exercise Files. Here's how to fix this:
- Open a Finder window and Navigate to /Applications/XAMPP/htdocs/SQL
- Control-click on the SQL folder and select "Get Info" from the context menu.
- Under "Sharing and Permissions" (you may need to open the disclosure triangle), in the "everyone" row, select "Read & Write."Then you can close the Info window.
- Now repeat the process for the three *.db files inside the folder.