Skip to content

SQL Fundamentals

SQL vocabulary plus your first two commands.

You have a database. Now what? You cannot drag and drop data into it. You need a language. SQL (Structured Query Language) is that language. Every command you will ever run against a database is written in SQL. This stage covers the vocabulary and your first two commands.

Definition

SQL (Structured Query Language) is the standard programming language for managing and querying data in relational database management systems. SQL is used to create, read, update, and delete data in databases.

1.SQL Keywords

SQL has a small set of core keywords. Here are the ones you will use most:

  • CREATE DATABASE - make a new database
  • USE - switch to a database
  • CREATE TABLE - make a new table
  • INSERT INTO - add rows
  • SELECT - read data
  • UPDATE - change existing rows
  • DELETE - remove rows
  • DROP TABLE - remove a table

2.Statement Structure

Most SQL statements follow a pattern:

KEYWORD thing_to_act_on
  condition
  more_options;

For example: "Create a table called users with columns for id and name." The database does exactly what you say.

Your First Commands

These two commands set up your workspace. Copy each one, paste it into the playground, and run it.

Create a database

CREATE DATABASE school;
USE school;

CREATE DATABASE makes a new database. USE switches to it. Always USE a database before creating tables.

See available databases

SHOW DATABASES;

SHOW DATABASES lists all databases you can access. Your new database should appear in the list.

Switch databases

USE school;
SELECT DATABASE();

USE switches your active database. SELECT DATABASE() shows which one you are in.

3.SQL Statement Anatomy

Every SQL statement follows a predictable structure. Understanding this pattern makes it easier to read and write any query:

ACTION thing_you_are_acting_on
  what_to_do_to_it
  additional_conditions;

For example, CREATE TABLE students adds a new table called students. INSERT INTO students adds rows to that table. SELECT * FROM students reads rows from it. The keyword always comes first, followed by what you are acting on, then any conditions or options.

4.Common Mistakes Beginners Make

  • Forgetting the semicolon at the end of a statement. Every SQL statement ends with a semicolon. Without it, the database does not know where one statement ends and the next begins.
  • Not using a database first. If you try to CREATE TABLE without running USE database_name first, MySQL returns an error because it does not know which database to put the table in.
  • Mixing up CREATE DATABASE and CREATE TABLE. CREATE DATABASE makes a new database (a container). CREATE TABLE makes a new table inside a database (a shelf). You need the database before you can create tables.
  • Spelling SQL keywords wrong. While MySQL keywords are not case-sensitive, misspelling them causes errors. CREATE is not the same as CRETE. Double-check your spelling.

5.What Happens Behind the Scenes

When you run CREATE DATABASE school, MySQL creates a new directory on the server to hold your tables. When you run USE school, MySQL switches your connection to that directory. When you run CREATE TABLE students, MySQL creates a new file inside that directory to store the table structure. When you INSERT data, MySQL writes rows into that file. When you SELECT data, MySQL reads from the file and returns the matching rows.

You do not need to understand these internals to use SQL, but knowing that databases are organized as files on disk helps explain why some operations are fast (reading a few rows) and others are slow (scanning millions of rows).

What does USE school; do?

Which command shows all databases?

Key Takeaways

  • SQL keywords are not case-sensitive but capitalizing them is conventional.
  • CREATE DATABASE makes a new database; USE switches to it.
  • SHOW DATABASES lists all available databases.
  • Always USE a database before creating tables in it.

Ready to test your knowledge?

Take a Quiz

Related Tutorials