Having access to your website’s data is very useful to any webmaster – it contains the records of your website’s visitors, subscribers, posts, and much more. But managing the data can be tricky. This is where SQL comes in. By learning basic SQL commands, you will be able to control your data with ease.
In this article, you will learn some basic SQL commands.
What is SQL?
Structured Query Language (SQL) is a language used to communicate with databases. It helps you to manage your website’s data.
All databases use a relational database management (RDBMS) system. The data or records are stored in tables.
List of Basic SQL Commands
To interact with your SQL database, you need to use commands known as statements. There are a lot of them, but you can make do with only a few common commands.
Hence, we’ve gathered the most common SQL commands below to help you out. Call it a cheat sheet, if you like.
Select
The select command is the most simple and basic one. It is used to request particular records from one or more tables.
In the example below, we select two columns from a table called BestMovies.
SELECT Title, Year FROM BestMovies;
The data retrieved from entering the command above will be shown in a result table. In this case, the returned table will contain the title and the release year of the movies.
Order By
Order by command allows you to sort the result table in an ascending or descending order. It can be very useful, especially if you have a lot of records and want to scan through them quickly.
Here’s an example of a command to sort the BestMovies table by the Year column:
SELECT FROM BestMovies ORDER BY Year;
Update
The update statement is just what the name states. It is used to modify existing records in your database.
In the example below, we want to change a record called HP into Harry Potter. The record is within the Title column:
UPDATE BestMovies SET Title=’Harry Potter’ WHERE Title=’HP’;
To put it simply, the “where” command is a specifier. It is always used together with other SQL statements. This is a way to ensure that the other statements only affect particular rows.
Here, it is used to define that only HP will be updated. If you forget it, all the titles in the table will be changed to Harry Potter. So be careful when updating your database!
Insert Into
After retrieving and modifying data, the next step is to learn how to add new records. Using insert into statement will add new rows and data to your table.
The following example will insert The Godfather and 1972 into BestMovies table:
INSERT INTO BestMovies (Title, Year) VALUES (‘The Godfather’, ‘1972’);
Delete
Delete statement allows to you delete records. Here’s a simple example of deleting Harry Potter record from BestMovies table:
DELETE FROM BestMovies WHERE = ‘Harry Potter’;
Again, don’t forget to specify which records you want to delete by using where command. Forgetting to specify can remove all your data.
Create Table
Another straightforward command. The create table statement is used to make new tables in the database. In the syntax below, we create the BestMovies table with three columns: Title, Producer, and Genre.
CREATE TABLE BestMovies Title varchar (255), Producer varchar (255), Genre varchar (255), );
Varchar is the data type of the column. It defines the kind of data the column can store. To learn more about it and other data types, go to the overview of SQL data types.
Drop Table
This is a dangerous command. Drop table allows you to delete an entire table and its records. Make sure to be very careful when using this command.
The example below deletes the BestMovies table:
DROP TABLE BestMovies;
Alter Table
The alter table command is used to modify, add, or delete columns in a table. Here’s a simple example of adding Producer column to BestMovies table:
ALTER TABLE BestMovies ADD Producer varchar (255);
Join table
Join table is a command that combines data from two or more tables. It brings together the records based on a shared column between the tables.
Let’s say we have two tables, BestMovies and Award-Winning. Both of them have a shared column called Title. We want to combine these tables to see what are the best movies that also won awards. Here’s how we do it:
SELECT * FROM BestMovies INNER JOIN Award-Winning ON BestMovies.Title = Award-Winning.Title
The example above uses the inner join type. There are three other basic types: right join, left join, and outer join.
- Inner join: only retrieves matching data from both tables
- Left join: retrieves all data from the left table (first table) and matching data from the right table (second table).
- Right join: retrieves all data from the right table (second table), and matching data from the left table (first table).
- Outer join: retrieves all data from both tables.
Additional Resources to Learn SQL
All the commands mentioned are just a fraction of what you can do with SQL. There are many other SQL statements you can learn out there. Here are some places so learn SQL:
Codeacademy
Codeacademy’s SQL course is divided into four parts. You’ll learn data manipulation, data retrieval, aggregate functions, and retrieval of multiple tables. If you are stuck in certain subjects, there are forums and articles you can refer to.
The course is free, but you have to go premium if you want to take the final quiz and project.
W3schools
SQL course by W3schools stands out for its short and solid explanations. Each subject is complemented by simple examples and interactive exercises.
Other than learning about commands and functions, you will also discover how to use SQL in database systems such as MySQL, SQL Server, Oracle, and many more. And the good news is, this course is completely free!
Udemy
Udemy can be an alternative for those who prefer video courses. The course ranges from the basics to SQL for particular purposes. Some advanced videos include SQL for data analytics and business intelligence, data science, marketers, and etc.
Provided by various experts, the courses are either free or paid.
If you want to see other alternatives, check out post on favorite websites to learn SQL. You can easily learn everything about this programming language – even if you are a complete beginner!
Conclusion
As a website creator, it is important to know how to access and control your website data. But it is hard to manage them manually once your website grows bigger and stores more data.
That is why learning SQL, the language to communicate with your database easily, will be very helpful and useful.
Hopefully, now that you’ve learned the most common SQL commands, you can put them into use and show off your skills!
