Production databases in SQL queries

SQL is a Structured Query Language which is a computer language for storing, manipulating, and retrieving data stored in a relational database. SQL is the most powerful data handling tool. Actionable advice to help you get the most versatile language and create beautiful, effective queries. SQL is effectively used to insert, search, update, delete, and modify database records. It doesn’t mean SQL can not do things beyond that. It can do a lot more other things as well. In SQL data is stored in the form of relations. This relation theory was suggested by Boyce and Chamberlin. The first company to release an altered version of SQL was relational software, and it was called Oracle V2. SQL become a standard of the American national standards institute (ANSI)in 1986 and the International Organization for Standardization (ISO) in 1987.

Let’s understand with the help of examples.

Example-1 :
SQL queries for creating a CUSTOMERS table for personal information as follows.

CREATE TABLE CUSTOMERS (
ID INT NOT NULL,
NAME VARCHAR (20) NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR (25),
ORDERS VARCHAR (155)
);

Example-2 :
SQL queries for creating the CUSTOMERS table are as follows.

CREATE TABLE CUSTOMERS (
CUS_ID INT NOT NULL,
CUST_NAME VARCHAR(20) NOT NULL,
DOB DATE,
STREET VARCHAR (200),
CITY VARCHAR (100),
STATE VARCHAR (100),
EMAIL_ID VARCHAR (256),
PRIMARY KEY ( CUST_ID)
);

Uses of the production database in SQL queries :

1. Business requirements – 
Identify stakeholders, focus on business outcomes, Discussion for optimal requirements, Ask great questions, and specific requirements and confirm them with stakeholders.

2. SELECT fields –
Using the SELECT statement will point the database to querying only the data you need to meet the business requirement. Let’s understand with the help of examples. For Example –

Inefficient –

SELECT * FROM Customers     

Efficient –

SELECT FirstName, Last name, Address,City,State,Zip  
FROM Customers       

3. Avoid SELECT DISTINCT – 
SELECT DISTINCT is a way to remove duplicates from a query. SELECT DISTINCT works by GROUPing all fields in the query.

Inefficient and inaccurate – 

SELECT DISTINCT FirstName, Last name, State 
FROM Customers       

Efficient and accurate –

SELECT FirstName, LastName, Address, City, State, Zip              
FROM Customers        

4. JOIN (not WHERE) –

SELECT Customers.CustomerID, Customers. Name, Sales. Last Sale.Date FROM Customers, Sales 
WHERE Customers.CustomerID = Sales.customerID 

This type of join creates a Cartesian join, also called a Cartesian product or CROSS JOINT  

INNER JOIN –                

SELECT Customers.CustomerID, Customers.Name , Sales.Last SaleDate  
from Customers 
INNER JOIN Sales ON customers.CustomerID  = 
Sales.CustomerID 

DBMS Systems are able to recognize WHERE joins and automatically run them as INNER Joins.

5. Use where instead of having to define filters –
The objective of an efficient SQL query must be useful to get only the required records needed from the database. For example, Let’s assume 300 sales have been made in the year 2017, and we want to query for the number of sales per customer in 2017.

SELECT Customers.customerID,Customers.Name,Count (sales.saleID)
FROM Customers
INNER JOIN Sales       
ON Customers.CustomerID = Sales.CustomerID        
GROUP BY Customers.CustomerID,Customers.Name      
HAVING Sales.Last SaleDate 
BETWEEN     
#1/1/2017# AND #12/31/2017#

6. Use a wildcard at the end –
The wildcard is used, especially in combination with an ending wildcard, the database is tasked with searching all records for a match anywhere within the selected field.

SELECT City FROM Customers                     
WHERE city LIKE '%char%' 

7. Run your query during the off-peak hour –
It will be always a good idea to enhance the performance and to avoid any production load which is just due to other activities like updating, patching, bug fixing scripting, and other scripts, etc. must be run at an off-peak time.   

The following criteria for your query are as follows.

• Selecting from large tables
• Cartesian joins
• Looping statements
• Nested sub-queries.

Features :

• High performance –
  SQL provides high-performance programming capability, a heavy workload, and a high uses database system. It gives various ways to describe the data.
• High availability –
  It is compatible with databases like MS Access, Microsoft SQL Server, MY SQL, Oracle database, etc. relational database management support SQL. It is easy to create an application extension for procedural programming.
• Scalability and flexibility –
  It is very easy to create new tables and previously created or not used tables can be dropped or deleted in databases.
• High security –
  It is easy to provide permission on tables, procedures, and views hence it gives security to your data.

Conclusion :
Designing objects with reusable queries. Other objects can reference may save us development time. We will need to redevelop the reference object. It tracks references from comments to the metadata when we do make changes then introduces a few problems. There are situations that may not need a reference due to a query being temporary or being easy to develop.