what is the sql keyword that adds rows to a table?

Introduction to SQL Keywords

In SQL, the keywords are the reserved words that are used to perform various operations in the database. In that location are many keywords in SQL and as SQL is case insensitive, information technology does not affair if we use for example SELECT or select.

List of SQL Keywords

The SQL keywords can be used as explained in the below examples for various operations.

1. CREATE

The CREATE Keyword is used to create a database, table, views, and index. We can create the table CUSTOMER as beneath.

CREATE Tabular array Client (CUST_ID INT PRIMARY KEY, NAME VARCHAR(50), State VARCHAR(twenty));

two. PRIMARY Central

This keyword uniquely identifies each of the records.

A Database in SQL can be created with the usage of CREATE DATABASE argument as below:

CREATE DATABASE DATABASE_NAME;

A View in SQL can exist created by using CREATE VIEW as below:

CREATE VIEW VIEW_NAME Every bit SELECT COLUMN1, COLUMN2, COLUMN3... FROM TABLE_NAME WHERE [Condition];

3. INSERT

The INSERT Keyword is used to insert the rows of information to a tabular array. We can insert the below rows to the already created CUSTOMER tabular array by using the queries as beneath.

INSERT INTO CUSTOMER VALUES (121,'Rajesh','Maharashtra'); INSERT INTO CUSTOMER VALUES(256,'Leela','Punjab'); INSERT INTO CUSTOMER VALUES(908,'Priya','Jharkhand'); INSERT INTO CUSTOMER VALUES(787,'Rohit','Upwards');

The above statements will insert the rows to the tabular array "CUSTOMER". We can see the result by using a simple SELECT statement below

SELECT * FROM CUSTOMER;

4. SELECT

This keyword is used to select the information from the database or table. The '*' is used in the select statement to select all the columns in a table.

SELECT NAME FROM CUSTOMER;

The result of the above query will display the cavalcade Proper noun from the CUSTOMER table every bit below

5. FROM

The keyword is used to indicate the table from which the information is selected or deleted.

6. Modify

The Keyword Modify is used to alter the columns in tables. The ALTER COLUMN statement modifies the information type of a column and the Alter TABLE modifies the columns by adding or deleting them.

Nosotros can modify the columns of the CUSTOMER table every bit below past adding a new column "Historic period".

ALTER Table CUSTOMER ADD Historic period INT; SELECT * FROM Client;

This query to a higher place will add the new column "Historic period" with values for all the rows as null. Also, the higher up statement uses another SQL keyword 'ADD'.

7. ADD

This is used to add together a column to the existing table.

8. Distinct

The keyword Singled-out is used to select distinct values. We can use SELECT Distinct to select only the distinct values from a table.

Permit us add a duplicate value for the state Punjab every bit below:

INSERT INTO CUSTOMER VALUES(178, 'Pooja', 'Punjab','null');

The customer table has now the below rows

At present nosotros can come across the distinct values for the column Land by using the below query:

SELECT Distinct(STATE) FROM CUSTOMER;

9. UPDATE

This keyword is used in an SQL statement to update the existing rows in a table.

UPDATE CUSTOMER Prepare STATE ='Rajasthan' WHERE CUST_ID= 121; SELECT * FROM Customer;

The CUST_ID with value 121 is updated with a new state Rajasthan.

10. Set

This Keyword is used to specify the column or values to exist updated.

11. DELETE

This is used to delete the existing rows from a table.

DELETE FROM Client WHERE NAME='Rajesh';

The in a higher place query will display the below equally the row with Proper noun as Rajesh is deleted from the result gear up.

While using the DELETE keyword, if we do non apply the WHERE clause, all the records will be deleted from the tabular array.

DELETE FROM Customer;

The above query will delete all the records of the Client table.

12. TRUNCATE

This is used to delete the data in a tabular array, merely information technology does non delete the structure of the table.

TRUNCATE Table CUSTOMER;

The above query only deletes the data but the structure of the tabular array remains. So in that location is no need to re-create the table.

xiii. Equally

The Keyword Equally is used as an alias to rename the column or table.

SELECT CUST_ID Equally CUSTOMER_ID, NAME Equally CUSTOMER_NAME FROM CUSTOMER;

The higher up statement will create the alias for the columns CUST_ID and NAME as below:

fourteen. ORDER BY

This is used to sort the result in descending or ascending order. This sorts the result by default in ascending order.

xv. ASC

This keyword is used for sorting the data returned by the SQL query in ascending order.

SELECT * FROM CUSTOMER ORDER By NAME ASC;

The higher up query will select all the columns from the CUSTOMER table and sorts the information by the NAME column in ascending social club.

16. DESC

This keyword is to sort the result prepare in descending order.

SELECT * FROM CUSTOMER ORDER By CUST_ID DESC;

The to a higher place query will sort all the selected fields of the table with the descending social club of CUST_ID.

17. Betwixt

This keyword is used to select values inside a given range. The below query uses the Between keyword to select the CUST_ID and Proper noun inside a given range of values for the CUST_ID.

SELECT CUST_ID, NAME FROM CUSTOMER WHERE CUST_ID Between 100 AND 500;

The above query will give the beneath consequence

xviii. WHERE

This keyword is used to filter the event set so that only the values satisfying the condition are included.

SELECT * FROM Customer WHERE Country ='Punjab';

The higher up query selects all the values from the table for which the state is Punjab.

19. AND

This keyword is used along with the WHERE clause to select the rows for which both the atmospheric condition are true.

SELECT * FROM CUSTOMER WHERE STATE ='Punjab' AND CUST_ID= 256;

The above query volition give the consequence as beneath

But if one of the conditions is not satisfied, then the query volition not return any issue every bit stated in the below query.

SELECT * FROM CUSTOMER WHERE STATE ='Punjab' AND CUST_ID= 121;

20. OR

This is used with the WHERE clause to include the rows in the event set in example of either condition is true.

The below SQL statement volition select the fields from the CUSTOMER tabular array if the state is Punjab or UP.

SELECT * FROM Client WHERE State='Punjab' OR STATE='Up';

In the case of the OR keyword, nosotros tin see from the above effect that in instance of whatever of the given conditions are true, that gets included in the result ready.

21. NOT

The keyword Not is used with a WHERE clause to include the rows in the effect fix where a condition is not true.

We can use the NOT keyword in the below query to not include the rows from the country Punjab as below.

SELECT * FROM CUSTOMER WHERE Not Country = 'Punjab';

The query will return the rows with the other states excluding Punjab in the result set every bit below:

22. LIMIT

This keyword retrieves the records from the tabular array in lodge to limit them based on the limit value.

SELECT * FROM Client LIMIT iii;

The above query will select the records from the table CUSTOMER but it will display merely the 3 rows of data from the table as below

23. IS NULL

The keyword IS NULL is used to cheque for NULL values.

The below query will show all the records for which the AGE cavalcade has NULL values.

SELECT * FROM Client WHERE AGE IS Cypher;

IS NOT NULL

This is used to search the Not NULL values.

SELECT * FROM CUSTOMER WHERE STATE IS NOT Zero;

As the column STATE has no null values, the higher up query will show the below result.

24. Drop

The DROP keyword tin be used to delete a database, table, view, column, index, etc.

25. Drib Column

We can delete an existing column in a tabular array by using a Drib COLUMN forth with an ALTER statement. Allow the states delete the column AGE by using the below query.

ALTER TABLE CUSTOMER Drop Cavalcade Historic period;

We can see that in the above result, the Age cavalcade is dropped.

26. DROP DATABASE

A database in SQL can be deleted by using the DROP DATABASE statement.

Driblet DATABASE DATABASE_NAME;

27. DROP TABLE

A table in SQL can be deleted by using a DROP TABLE statement.

DROP TABLE TABLE_NAME;

We tin delete the tabular array Client past using the Drib TABLE keyword as below.

But we need to exist careful while using Drib TABLE as it will remove the table definition forth with all the data and indexes etc.

28. GROUP BY

This is used forth with the aggregate functions similar COUNT, MAX, MIN, AVG, SUM, etc. and groups the effect set. The below query volition grouping the CUST_ID co-ordinate to the various states.

SELECT COUNT(CUST_ID),Country FROM CUSTOMER Group By STATE;

The consequence shows the count of different CUST_ID grouped by states.

29. HAVING

This keyword is used with aggregate functions and GROUP Past instead of the WHERE clause to filter the values of a result set.

SELECT COUNT(CUST_ID),Land FROM Customer GROUP BY STATE HAVING COUNT(CUST_ID)>=two;

The above query will filter the result set past displaying only those values which satisfy the condition given in the HAVING clause.

The above upshot prepare shows the values for which the count of the customer ids is more than than two.

30. IN

The IN keyword is used within a WHERE clause to specify more than than 1 value or we can say that it can exist used instead of the usage of multiple OR keyword in a query.

The beneath query will select the records for the states Maharashtra, Punjab and UP by the use of the IN keyword.

SELECT * FROM CUSTOMER WHERE Country IN ('Maharashtra','Punjab','Upward');

The above event set shows the usage of IN keyword which selects the records just for u.s. specified inside the IN clause.

31. Bring together

The keyword JOIN is used to combine the rows between two or more tables with related columns among the tables. The JOIN can be INNER, LEFT, Correct, OUTER JOIN, etc.

Lets usa take some other table 'CUST_ORDER' every bit an instance.

We can perform an inner join of the CUSTOMER and CUST_ORDER tables as beneath

SELECT Customer.NAME, CUSTOMER.STATE, CUST_ORDER.ITEM_DES FROM Client INNER Bring together CUST_ORDER ON Customer.CUST_ID =CUST_ORDER.ID;

The above query will join the two tables Customer and CUST_ORDER on the columns CUST_ID and ID and display simply the values which are present in both the tables.

This upshot shows the matching records for cust_id 121,908 and 178 which are common in both the tables. Only the other cust_ids are excluded equally they are not present in the CUST_ORDER table. Similarly, the other JOINs can be performed.

32. UNION

The UNION keyword is used to combine the distinct values of two or more select statements.

SELECT CUST_ID FROM CUSTOMER UNION SELECT ID FROM CUST_ORDER;

The to a higher place query will show the beneath result.

33. Wedlock ALL

This keyword combines two or more select statements simply allows duplicate values.

SELECT CUST_ID FROM Customer UNION ALL SELECT ID FROM CUST_ORDER;

The in a higher place upshot shows that UNION ALL allows the duplicate values which would not be nowadays in the case of UNION.

34. EXISTS

The keyword EXISTS checks if a certain record exists in a sub-query.

SELECT NAME FROM CUSTOMER WHERE EXISTS (SELECT ITEM_DES FROM CUST_ORDER WHERE CUST_ID = ID);

The in a higher place query will return true as the sub-query returns the below values.

35. LIKE

This keyword is used to search forth with a WHERE clause for a detail pattern. Wildcard % is used to search for a pattern.

In the below query allow us search for a pattern 'ya' which occurs in the column 'Proper name'.

SELECT Proper noun FROM CUSTOMER WHERE NAME LIKE '%ya';

36. Instance

This keyword is used to brandish different output co-ordinate to different weather.

SELECT CUST_ID, Name, Example WHEN State = 'Punjab' And so "State is Punjab" ELSE "State is Not Punjab" END AS Output FROM CUSTOMER;

A few other keywords are DEFAULT used to provide a default value for a cavalcade, UNIQUE used to ensure all the values in a cavalcade are unique, etc.

Decision

The various keywords in SQL provide flexibility in designing a database, tables, etc. They provide the designer with many features which becomes very useful while making whatsoever changes after the design is completed.