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 tools. Actionable advice to help you get the most versatile language and create beautiful, effective queries. SQL is effectively used to insert, search, update, delete, modify database is 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 and altered version of SQL was relational software, and it called as Oracle V2. SQL become a standard of American national standards institute (ANSI)in 1986 and the International Organization for Standardization (ISO) in 1987.
Let’s understand with the help of examples.
Attention reader! Don’t stop learning now. Learn SQL for interviews using SQL Course by GeeksforGeeks.
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) );
SQL queries for creating CUSTOMERS table 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, 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 –
SELECT * FROM Customers
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 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 your query as follows.
- Selecting from large tables
- Cartesian joins
- Looping statements
- Nested sub-queries.
- High performance –
SQL provides high-performance programming capability, heavy workload, and a high uses database system. It gives various ways to describe the data more.
- 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 delete in databases.
- High security –
It is easy to provide permission on tables, procedures, and views hence it gives security to your data.
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 few problems. There are situations that may not need a reference due to a query being temporary or being easy to develop.