SQL is a standardized language for creating, managing, and performing various operations on relational databases.
SQL, which was first developed in the 1970s, is now widely used by database administrators, developers writing data integration scripts, and data analysts setting up and running analytical queries.
SQL queries and other operations are expressed as statements that are compiled into programs allowing users to add, modify, and retrieve data from the database.
The American National Standards Institute (ANSI) approved an official SQL standard in 1986, and the International Organization for Standardization (ISO) followed suit in 1987.
Where Can I Write SQL Programs?
Structured Query language (SQL) can be used to perform various operations and manage relational databases. It is a highly standardized programming language.
As SQL is a highly demanded skill in the whole world, many people consider learning it and writing their own SQL programs, which leaves them with the question; where should they write their programs?
Typically, an Integrated Development Environment (IDE) is used to write SQL programs. It is a type of software that allows users to write programs in various languages.
Moreover, an IDE has a user-friendly interface along with unique features that can make it easier for you to write your code. There are several IDEs that are based on SQL which you can use to write your programs.
Whether you are an experienced SQL writer or a beginner, you should always use a SQL IDE for writing your programs. Some examples of famous IDEs are DbVisualizer, MySQL, and SQLGate.
How Do I Write A SQL Program?
After you have chosen the perfect IDE for yourself, you may move on to writing your own SQL program. An SQL program comprises various queries which you need to fill in order to construct your data table.
Here are the queries present in a code:
1. SELECT Statement
This is the starting point of your SQL query which is used to retrieve data from the data tables. In this syntax, you need to specify all the column names of your tables. Make sure to add a comma between the names.
2. WHERE
WHERE allows your Query to be more specific as it can filter the data under specific conditions. When you write the WHERE clause in your code, you need to specify the filtering condition right after it.
3. BETWEEN
BETWEEN is an operator which is used for conditional queries. It clarifies the data that falls into the specified minimum and maximum values in a query.
4. LIKE
Another essential operator is LIKE which is a logic operator. It is used with the WHERE condition and it applies a distinct filtering pattern. Conjunction such as the percentage sign (%) is used with this operator.
5. IN
It is another operator used with the WHERE clause. It basically allows the user to apply multiple value filters with the clause.
6. ORDER BY Statement
The ORDER BY Statement is a query statement that allows you to sort your data in the table. It sorts the data according to the stated columns either in ascending or descending order.
In the code, you can use keywords such as ASC for ascending order and DESC for descending order.
7. DISTINCT
This operator is mainly used to eliminate any duplicate data in your code so the result contains different values.
How Do I Output A SQL File?
Once you are done writing your SQL script, you need to output the SQL file in order to execute it. To output the file, you need to select the required database then right-click and click on “Export Data”.
The IDE will ask you to select the export format so just select “SQL”. Then choose the tables you want to export the data on under the “source” tab.
After that, it will ask you to confirm the specific fields and columns you want to export, just select the ones you want to output and click on “Finish” to finally output your SQL program file.
Best SQL Program Examples with Output
Here are some SQL program examples with SQL codes and queries along with the executed tables:
Table To Store Information About Train Stations In Different Cities:
CREATE TABLE STATION
(ID INTEGER PRIMARY KEY,
CITY CHAR(20),
LAT_N REAL,
LONG_W REAL);
INSERT INTO STATION VALUES (23, ‘London City’, 18, 134);
INSERT INTO STATION VALUES (54, ‘Guernsey City’, 50, 99);
INSERT INTO STATION VALUES (73, ‘Manchester City, 67, 119);
To Update: You can simply add
Update Table Station
SET City= ‘Paris’ where ID=23
To Retrieve Information, Use Select Statement
Select * from Station (retrieves all)
Select * from Station where id=54 (retrieves only where id is 54, which is Guernsey City.
Table To Insert Information About Employees Working In An Office:
CREATE TABLE EMPLOYEE
(EMPLOYEE_ID STRING PRIMARY KEY,
NAME CHAR
SALARY CURRENCY);
INSERT INTO EMPLOYEE VALUES (R34T5, ‘Anthony Eleven’, $6500)
INSERT INTO EMPLOYEE VALUES (G25BH, ‘Ryan Goldsmith’, $5750)
INSERT INTO EMPLOYEE VALUES (K83PO, ‘Elisa Parker’, $6250)
Another Full Example of MySQL:
~$ sudo mysql -u root
The following command creates a database called “Retail Store”
mysql> create database retail store;
mysql> use retail;
We will first create a table called “customer” using the create table command
mysql> create table customer (
-> cust_id int primary key,
-> age int,
-> location varchar(20),
-> gender varchar(20)
-> );
We will create the second table which is called “orders”
mysql> create table orders (
-> order_id int primary key,
-> date date,
-> amount decimal(5,2),
-> cust_id int,
-> foreign key (cust_id) references customer(cust_id)
-> on delete cascade
-> );
mysql> show tables;+——————+
| Tables_in_retail_Store |
+——————–+
| customer |
| orders |
+——————+
mysql> desc orders;+———-+————–+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+——+——-+—–+—–+——-+——-+
| order_id | int(11) | NO | PRI | NULL | |
| date | date | YES | | NULL | |
| amount | decimal(5,2) | YES | | NULL | |
| cust_id | int(11) | YES | MUL | NULL | |
+——-+——–+——+—–+———+——-+
Conclusion
Here, we have discussed in detail what SQL and information retrieval are. We have also discussed how to store information, update information, and what are some other possibilities with SQL and provided some best sql program examples.
Now, all you have to do is go on and create your own databases for your applications!
Related Posts to sql program examples:
