Basic queries to get started on SQL
Introduced in 1974, SQL or Structured Query Language is a domain-specific language used in programming and for managing data held in a relational database management system or for stream processing in a relational data stream management system. It is the most used database environment and the third most popular language. SQL is the standard language for dealing with relational databases in big tech companies like Facebook, Instagram, Accenture, Dell, etc. Essentially, SQL language is used to query a database. Below, we look at the eight SQL queries all data scientists should know. Where Clause The WHERE clause filters records based on input statements. Developers can use extract only those records that fulfil a specified condition. They can use the * operator to display the entire table and narrow down the results using the ‘WHERE’ keyword that displays specific rows. Syntax: SELECT column1, column2, … FROM table_name WHERE [condition] Date Functions The usual date format is YYYY-MM-DD, with the date-time format being YYYY-MM-DD HH:MI: SS. The data function allows developers to find a date in the present or future and dissect it into components for the month, the year, the day or a combination facilitating large-scale database design and maintenance. Syntax: select current_timestamp; — date and time, standard ANSI SQL so compatible across DBs select getdate(); — date and time, specific to SQL Server SQL Joins A JOIN clause combines rows from two or more tables based on a related column. There are four types of Joins in SQL– the inner, left, right and full. The Inner Join clause creates a new table by combining rows with matching values in two or more tables. The Right Join returns all records from the right table matching with the left table, and the Left Join returns all records from the left table matching with the right. Lastly, the Full Join returns all records when there is a match in the left or right table. Syntax for Inner Join: SELECT column_name(s) FROM table1 INNER JOIN table2 ON table1.column_name = table2.column_name; Correlated Subqueries Correlated Subqueries are used for row-by-row processing and select data from a table referenced in the outer query. The function works where each subquery is executed once for every outer query row. This allows developers to read every row in a table and compare their values against related data. A correlated subquery is evaluated once for every row processed by the parent statement, such as SELECT, UPDATE, or DELETE. Syntax: SELECT column1, column2, …. FROM table1 outer WHERE column1 operator (SELECT column1, column2 FROM table2 WHERE expr1 = outer.expr2); Case Clause The CASE clause implements the logic when the developer wants to set the value of one column based on the values in other columns. The function goes through conditions and returns a value when the first condition is met. It works till it finds a true condition, and if no condition is true, it returns the value in the ELSE clause. The Case clause is similar to the IF-ELSE statement in Excel. Syntax: CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 WHEN condition THEN resultN ELSE result END; Insert Values/ Rows into Tables This query allows developers to easily insert multiple values or rows in a table with a single query. Inserting values in a table is a DML (Data manipulation language) operation to add data to the database. Syntax: INSERT INTO table_name (column_list) VALUES (value_list_1), (value_list_2), … (value_list_n); SQL Queries to Retrieve All Data From A Table One of the most used DLM commands, the SELECT query, is used to select data from a database, and the retrieved data is stored in a result table called the result-set. The query returns a result set of records from one or more tables. Syntax: SELECT column1, column2, … FROM table_name; Get Average Values for Data The average value syntax helps developers find the average value in the given data. It is an aggregate function that calculates the average value of a numerical dataset. The function can be used with the SELECT query for retrieving data from a table. Syntax: SELECT AVG( column_name ) FROM table_name Find Duplicate records This query helps developers find duplicate records in the table using a unique identifier. It is done via the GROUP BY statement in SQL that arranges identical data into groups. For instance, if a column has the same values in different rows, the tool will arrange those rows in a group. Duplicate values can be found in two steps: Define criteria for duplicates.Write a query to search for duplicates.
The Case clause is similar to the IF-ELSE statement in Excel.
The Case clause is similar to the IF-ELSE statement in Excel.
[]
["Go", "stream processing", "programming_languages:R", "AI", "ML", "programming_languages:Go", "programming_languages:SQL", "RAG", "SQL", "R"]
["AI", "ML", "RAG", "R", "SQL", "Go", "stream processing", "programming_languages:R", "programming_languages:SQL", "programming_languages:Go"]
Avi Gopani is a technology journalist that seeks to analyse industry trends and developments from an interdisciplinary perspective at Analytics India Magazine. Her articles chronicle cultural, political and social stories that are curated with a focus on the evolving technologies of artificial intelligence and data analytics.