How to Use SQL in Your Databases - SQL Bootcamp: Learn the Basics of SQL Programming in 2 Weeks (2016)

SQL Bootcamp: Learn the Basics of SQL Programming in 2 Weeks (2016)

Chapter 7. How to Use SQL in Your Databases

This chapter will teach you how to apply SQL commands on your own databases. By reading this material, you’ll be able to interact with relational databases using the SQL computer language. This material is extremely important because it will help you master the basics of SQL in just 2 weeks.

How to Create a Database

To create a new database, you should use the CREATE DATABASE command. Here’s the syntax that you should follow:

Important Note: Relational database management systems require unique database names.

Let’s use the CREATE DATABASE command to generate a new database.

CREATE DATABASE sample;

The command given above creates a new database named“sample.”

Important Note: You won’t be able to create a new database if you don’t have admin privileges.

How to Delete a Database

In the SQL language, you use the DROP DATABASE command to delete an active database. Use the following syntax:

DROP DATABASE (insert the name of your database);

For example, let’s say you want to delete a database named“sample.” Here’s the SQL command you need to use:

DROP DATABASE sample;

Important Note: You should be extremely careful when using this command. Keep in mind that deleting a database involves permanent loss of data.

How to Select a Database

If you own multiple databases, you have to make sure that you are performing your desired operations on the right database/s. You should utilize the USE command to choose an existing database. Analyze the following syntax:

USE (insert name of the database here)

For instance, to select a database named“sample,” use the following SQL command:

USE sample

How to Create a Table

If you are creating a new table, you should name that table and define its columns and supported data types. You should use the CREATE TABLE command to accomplish this task. Here’s the syntax you should follow:

Basically,“CREATE TABLE” is a keyword that informs the system about your desire to create a new table. The identifier or name of your table comes after the CREATE TABLE command.

Then, create a list that defines the columns and data types that you want to use. Don’t worry if this is a bit confusing. You’ll understand this once you have analyzed the example given below.

How to Create a New Table from an Existing One

You may copy an existing table by combining two commands: SELECT and CREATE TABLE.

By default, the table that you’ll get will have the column definitions of the old one. However, you may select certain columns from the old table and discard the others. That means you may modify the new table according to your needs.

If you’ll succeed in using this command, the new table will acquire the current values of the old table. Here’s the syntax that you should use:

For instance, you would like to use a table named EMPLOYEES to generate a new one (let’s say you’d like to call it“COMPENSATION”). Then, you want to copy two of the columns inside the EMPLOYEES table: NAME and SALARY. To accomplish this task, you can use the following SQL code:

CREATE TABLE COMPENSATION AS

SELECT NAME, SALARY

FROM EMPLOYEES;

The code given above creates a new table named COMPENSATION, which has two columns: NAME and SALARY. Additionally, these columns will acquire the values found in the old table (i.e. EMPLOYEES).

How to Delete a Table

You can use the DROP TABLE command to delete a table and all the information it contains (e.g. data, constraints, indexes, etc.).

Important Note: You have to be careful when using this command. Remember that it involves the permanent removal of stored information. If you’ll drop the wrong table, you will face serious problems regarding your database.

The syntax of this command is:

DROP TABLE (insert the table’s name here);

For example: DROP TABLE sample

The command given above deletes a table named“sample” from your database.

How to Add New Data Rows

The INSERT INTO command allows you to add new data rows to an existing table. This command involves two syntaxes:

The First Syntax

You should use this syntax if you want to add data into certain columns.

The Second Syntax

You must use this syntax if you want to add values to all of the columns of your table. That means you won’t have to identify the columns you are working on. However, make sure that the sequence of the values is the same as that of the existing columns in the table.

How to Retrieve Data from a Table

You may use the SELECT command to retrieve data from a table. Here, SQL will present the search results as a new table. These new tables are known as“result sets.”

The syntax of the SELECT command is:

In this syntax, column1, column2, etc., are the fields that you like to retrieve. If you like to retrieve all of the fields inside a table, you may use this syntax:

The WHERE Clause

WHERE is an SQL clause that specifies a condition while retrieving information from your chosen tables.

If your specified condition is met, this clause will retrieve specific values from your table. In general, you should use WHERE to filter and retrieve the records that you need.

You may also use WHERE in other SQL commands such as DELETE and UPDATE. You’ll learn about these commands later on.

The syntax that you should use is:

You may use logical or comparison operators to set a condition for your WHERE clause.

How to Combine Various Conditions

SQL allows you to combine different conditions on your database queries. You just have to include the OR and AND operators in your SQL commands. SQL users refer to OR and AND as conjunctive operators.

Basically, the conjunctive operators allow you to perform multiple comparisons in a single SQL command. Let’s discuss OR first:

OR

You may use this operator to combine various conditions in a command’s WHERE clause. The syntax of this operator is:

N represents the quantity of conditions that you can combine using OR. Your SQL statements will perform an action only if one of your specified conditions is true.

AND

This operator allows you to place several conditions in the WHERE clause of an SQL command. Here’s the syntax that you should use:

“N” represents the quantity of conditions that you can combine. Keep in mind that your SQL command will only perform an action if all of the conditions are true.

How to Modify Existing Records

In the SQL language, you may edit existing records using the UPDATE query. This query, which is applied on the WHERE clause, allows you to edit data rows. Here’s the syntax that you should use:

How to Delete Records

If you want to delete records, you may use SQL’s DELETE Query. You can combine this query with SELECT to delete certain rows. On the other hand, you may use DELETE as a standalone query to delete all of the data rows. Here’s the syntax of this query:

DELETE FROM the_table’s_name

WHERE [specify you condition/s];

If you need to remove all the records from a table, you may simply remove the WHERE clause. Thus, the syntax will be:

DELETE FROM the_table’s_name;

How to Perform Comparisons Through Wildcard Operators

In SQL, you may use wildcard operators to compare a value against similar values. You just have to include these operators in the LIKE clause of your SQL commands. Here are the wildcard operators that you can use with LIKE:

· The underscore (i.e.“_”)

· The percent symbol (i.e.“%”)

You should use an underscore if you want to represent a single character or number. You must use the percent sign, on the other hand, if you want to represent, 0, 1, or several characters. You may combine these wildcard operators in your SQL statements.

Here is the syntax of the wildcard operators:

How to Use the TOP Clause

The TOP clause allows you to retrieve a number or percentage from your data tables.

Important Note: Some databases are not compatible with this clause. For instance, MySQL uses LIMIT to retrieve records.

The syntax of a SELECT command with the TOP clause is:

How to Sort Data

SQL offers ORDER BY, a clause that sorts data in descending or ascending order, depending on the column/s you use as a basis. The syntax that you should is:

You may include multiple columns in this clause. However, make sure that all of the columns you want to use are inside the column-list.

How to Mix the Results of Multiple SELECT Commands

In SQL, you may combine results from multiple SELECT commands while preventing redundant rows. You just have to use the UNION clause.

To use this clause, your SELECT statements should have the same quantity of selected columns and column expressions. The statements must also have the same type of data and column arrangement. However, the statements don’t need to have identical lengths.

The syntax for this clause is: