Beginner’s Guide: How To…

SQL JOIN SQL, JOIN means "to combine two or more tables". In SQL, JOIN clause is used to combine the records from two or more tables in a database. Types of SQL JOIN INNER JOIN In SQL, INNER JOIN selects records that have matching values in both tables as long as the condition is satisfied. It returns the combination of all rows from both the tables where the condition satisfies. Syntax SELECT table1.column1, table1.column2, table2.column1,....FROM table1 INNER JOIN table2 ON table1.matching_column = table2.matching_column; Example SELECT EMPLOYEE.EMP_NAME, PROJECT.DEPARTMENT FROM EMPLOYEE INNER JOIN PROJECT ON PROJECT.EMP_ID = EMPLOYEE.EMP_ID; LEFT JOIN The SQL left join returns all the values from left table and the matching values from the right table. If there is no matching join value, it will return NULL. Syntax SELECT table1.column1, table1.column2, table2.column1,.... FROM table1 LEFT JOIN table2 ON table1.matching_column = table2.matching_column; Example SELECT E...

 SQL Aggregate Functions COUNT FUNCTION COUNT function is used to Count the number of rows in a database table. It can work on both numeric and non-numeric data types. COUNT function uses the COUNT(*) that returns the count of all the rows in a specified table. COUNT(*) considers duplicate and Null. Syntax COUNT(*) or COUNT( [ALL|DISTINCT] expression ) Example SELECT COUNT(*) FROM PRODUCT_MAST; SELECT COUNT(*) FROM PRODUCT_MAST; WHERE RATE>=20; SELECT COUNT(DISTINCT COMPANY) FROM PRODUCT_MAST; SELECT COMPANY, COUNT(*) FROM PRODUCT_MAST GROUP BY COMPANY; SELECT COMPANY, COUNT(*) FROM PRODUCT_MAST GROUP BY COMPANY HAVING COUNT(*)>2; SUM FUNCTION Sum function is used to calculate the sum of all selected columns. It works on numeric fields only. Syntax      SUM() or SUM( [ALL|DISTINCT] expression ) Example SELECT SUM(COST) FROM PRODUCT_MAST; SUM() with WHERE SELECT SUM(COST) FROM PRODUCT_MAST WHERE QTY>3; SUM() with GROUP BY SELECT SUM(COST) FROM PRODUCT_MAST WH...

 SQL Clauses GROUP BY SQL GROUP BY statement is used to arrange identical data into groups. The GROUP BY statement is used with the SQL SELECT statement. The GROUP BY statement follows the WHERE clause in a SELECT statement and precedes the ORDER BY clause. The GROUP BY statement is used with aggregation function. Syntax      SELECT column      FROM table_name      WHERE conditions      GROUP BY column      ORDER BY column Example      SELECT COMPANY, COUNT(*)      FROM PRODUCT_MAST      GROUP BY COMPANY HAVING HAVING clause is used to specify a search condition for a group or an aggregate. Having is used in a GROUP BY clause. If you are not using GROUP BY clause then you can use HAVING function like a WHERE clause Syntax      SELECT column1, column2 FRO      M table_name      WHERE conditions      GROUP ...

 SQL Sub Query A Subquery is a query within another SQL query and embedded within the WHERE clause. Important Rule: A subquery can be placed in a number of SQL clauses like WHERE clause, FROM clause, HAVING clause. You can use Subquery with SELECT, UPDATE, INSERT, DELETE statements along with the operators like =, <, >, >=, <=, IN, BETWEEN, etc. A subquery is a query within another query. The outer query is known as the main query, and the inner query is known as a subquery. Subqueries are on the right side of the comparison operator. A subquery is enclosed in parentheses. In the Subquery, ORDER BY command cannot be used. But GROUP BY command can be used to perform the same function as ORDER BY command Subqueries with the Select Statement SQL subqueries are most frequently used with the Select statement. Syntax:      SELECT column_name      FROM table_name      WHERE column_name expression operator(SELECT column_name FROM tab...

 SQL Index Indexes are special lookup tables. It is used to retrieve data  from the database very fast. An Index is used to speed up select queries and where clauses. But it shows down the data input with insert and update statements. Indexes can be created or dropped without affecting the data. An index in a database is just like an index in the back of a book. Create Index statement           CREATE INDEX index_name ON table_name (column1, column2, ...); Unique Index statement Syntax           CREATE UNIQUE INDEX index_name ON table_name (column1, column2, ...); Example           CREATE UNIQUE INDEX websites_idx ON websites (site_name); Drop Index Statement Syntax         DROP INDEX index_name; Example               DROP INDEX websites_idx;

Views in SQL  Views in SQL are considered as a virtual table. A view also contains rows and columns.  To create the view, we can select the fields from one or more tables present in the database.  A view can either have specific rows based on certain condition or all the rows of a table. Creating view A view can be created using the CREATE VIEW statement. We can create a view from a single table or multiple tables. Syntax                CREATE VIEW view_name AS                SELECT column1, column2.....                FROM table_name                WHERE condition; Creating View from a single table                CREATE VIEW DetailsView AS                SELECT NAME, ADDRESS       ...

 SQL Operator An SQL operator is a special word or character used to perform tasks. These tasks can be anything from complex comparisons, to basic arithmetic operations. Think of an SQL operator as similar to how the different buttons on a calculator function. SQL operators are primarily used within the WHERE clause of an SQL statement. This is the part of the statement that is used to filter data by a specific condition or conditions. There are six types of SQL operators that we are going to cover: Arithmetic, Bitwise, Comparison, Compound, Logical and String.

Data Query Language  DQL is used to fetch the data from the database.  It uses only one command:  SELECT  a. SELECT: This is the same as the projection operation of relational algebra. It is used to select the attribute based on the condition described by WHERE clause.       Syntax:  SELECT expressions FROM TABLES WHERE conditions;       Example:  SELECT emp_name FROM employee WHERE age > 20;

Data Control Language DCL commands are used to GRANT and TAKE BACK authority from any database user. Here are some commands that come under DCL: Grant It is used to give user access privileges to a database. Example:  GRANT SELECT, UPDATE ON MY_TABLE TO SOME_USER, ANOTHER_USER; Revoke It is used to take back permissions from the user Example: REVOKE SELECT, UPDATE ON MY_TABLE FROM USER1, USER2; Transaction Control Language TCL commands can only use with DML commands like INSERT, DELETE and UPDATE only. These operations are automatically committed in the database that's why they cannot be used while creating tables or dropping them. Here are some commands that come under TCL: COMMIT Commit command is used to save all the transactions to the database. Syntex: COMMIT; Example: DELETE FROM CUSTOMERS WHERE AGE = 25; COMMIT; ROLLBACK Rollback command is used to undo transactions that have not already been saved to the database. Syntex: ROLLBACK; Example: DELETE FROM CUSTOMERS WHERE AGE =...

 Data Manipulation Language DML commands are used to modify the database. It is responsible for all form of CHANGES in the database. The command of DML is not auto-committed that means it can't permanently save all the changes in the database. They can be rollback. Here are some commands that come under DML INSERT UPDATE DELETE Data Manipulation Language - INSERT      INSERT:            The INSERT statement is a SQL query. It is used to insert data into the row of a table.      Syntax:                INSERT INTO TABLE_NAME (col1, col2, col3,.... col N)           VALUES (value1, value2, value3, .... valueN);           OR           INSERT INTO TABLE_NAME VALUES (value1, value2, value3, .... valueN);      Example:           INSERT INTO XYZ (Aut...

SQL Commands in Details  Data Definition Language (DDL) DDL changes the structure of the table like creating a table, deleting a table, altering a table, etc. All the command of DDL are auto-committed that means it permanently save all the changes in the database. Here are some commands that come under DDL: CREATE It is used to create a new table in the database.  Syntax                                         CREATE TABLE TABLE_NAME (COLUMN_NAME DATATYPES[,....]); Example CREATE TABLE EMPLOYEE(Name VARCHAR2(20), Email VARCHAR2(100), DOB DATE); ALTER It is used to alter the structure of the database. This change could be either to modify the characteristics of an existing attribute or probably to add a new attribute. Syntax: ALTER TABLE table_name ADD column_name COLUMN-definition; ALTER TABLE MODIFY(COLUMN DEFINITION....); Example: ALTER TABLE STU_...

  SQL Commands SQL commands are instructions. It is used to  communicate with the database. It is also used to  perform specific tasks, functions, and queries of data. SQL can perform various tasks like create a table, add  data to tables, drop the table, modify the table, set  permission for users. Types of SQL Commands There are five types of SQL commands:  DDL DML DCL TCL  DQL

Introduction to SQL   • SQL stands for Structured Query Language. It is used for storing and managing data in Relational Database Management System (RDBMS).  • It is a standard language for Relational Database System. It enables a user to create, read, update and delete relational databases and tables.  • All the RDBMS like MySQL, Informix, Oracle, MS Access and SQL Server use SQL as their standard database language.  • SQL allows users to query the database in a number of ways, using English-like statements. SQL follows the following rules:  • Structure query language is not case sensitive. Generally, keywords of SQL are written in uppercase.  • Statements of SQL are dependent on text lines. We can use a single SQL statement on one or multiple text line.  • Using the SQL statements, you can perform most of the actions in a database.  • SQL depends on tuple relational calculus and relational algebra. Introduction to  SQL Process. Wh...

AWS recently announced the general availability of Lambda Function URLs, a feature that lets developers directly configure a HTTPS endpoint and CORS headers for a Lambda function without provisioning other services. With the new feature developers can avoid relying on the Amazon API Gateway or the Application Load Balancer to map a Lambda function to a HTTP call. Each function URL is globally unique and can be associated with a function’s alias or the function’s ARN, implicitly invoking the latest version.  Alex Casalboni , principal developer advocate at AWS, explains the scenarios where to use the new feature helps:  Function URLs are best for use cases where you must implement a single-function microservice with a public endpoint that doesn’t require the advanced functionality of API Gateway, such as request validation, throttling, custom authorizers, custom domain names, usage plans, or caching. For example, when you are implementing webhook handlers, form validators, mo...