Structured Query Language (SQL) is a powerful tool for managing and manipulating data within relational databases. When it comes to inserting values into tables, SQL provides several methods to accommodate both single and multiple values. Check out this recipe to explore the various techniques to enter values into a table, catering to specific needs like uniqueness, counting, and updating.
Table of Contents
- Recipe Objective - SQL Values - How to Enter Single and Multiple Values in a Table in SQL?
- How to Insert Single Values in a Table?
- SQL Insert Values into a Table - Example
- How to insert multiple values into a table?
- SQL Insert Multiple Values Using SELECT
- Unique Values and Counting
- How to Ensure SQL Unique Values?
- SQL Count Unique Values
- SQL Update Values
- Enhance your SQL Skills with ProjectPro!
- How to Insert Single Values in a Table?
How to Insert Single Values in a Table?
Basic Syntax
To insert a single value into a table, the basic SQL syntax is as follows:
INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);
For example:
INSERT INTO employees (first_name, last_name, salary)
VALUES ('John', 'Doe', 50000);
This inserts a new record into the 'employees' table with the specified values.
SQL Insert Values into a Table - Example
Please refer to the previous tutorial to learn how to create a table –
Tables in SQLWe have successfully created a table structure that looks something like –
Customer_id Customer_name Age City Country
The next step is to insert values into the customers table. We will enter the values as shown below.
Customer_id Customer_name Age City Country
101 Thomas Shelby 30 Birmingham England
102 Grace Burgess 28 Dublin Ireland
103 Alfie Solomons 40 London England
104 Michael Gray 22 New York USA
105 May Carleton 29 Sheffield England
You can insert values into the table using the INSERT statement. You can –
Insert values without mentioning the columns
Insert values while mentioning the columns
Let us see both of these methodologies.
1) Insert values without mentioning the columns
Syntax:INSERT INTO table_name VALUES(value1, value2,...);
You must enter values in a sequence that matches the column sequence.
Code:
INSERT INTO customers VALUES (101,"Thomas Shelby",30,"Birmingham","England");
2) Insert values while mentioning the columns
Syntax:INSERT INTO table_name(column_name1,column_name2,...) VALUES (value1,value2,...)
The sequence of values must match the column sequence. You can enter the customer_id in the second place, but make sure that you mention customer_id in the second place as well in the columns list.
Code:
INSERT INTO customers(customer_name,customer_id, age,city,country)
VALUES("Grace Burgess",102, 28,"Dublin","Ireland");
How to insert multiple values into a table?
You can enter multiple values into a table using one single query as follows:-
Code:
INSERT INTO customers (customer_id,customer_name ,age,city,country)
VALUES (103,"Alfie Solomons", 40,"London","England"),
(104,"Michael Gray",22,"New York","USA"),
(105,"May Carleton",29,"Sheffield","England");
SQL Insert Multiple Values Using SELECT
Another approach is to use the SELECT statement to fetch values from another table and insert them into the target table:
INSERT INTO table_name (column1, column2, column3, ...)
SELECT column1, column2, column3, ...
FROM source_table
WHERE condition;
Become a SQL expert with ProjectPro's hands-on learning. Elevate your career prospects and conquer the world of data management.
Unique Values and Counting
How to Ensure SQL Unique Values?
To maintain uniqueness in a column, you can use the UNIQUE constraint when defining the table:
CREATE TABLE table_name (
column1 datatype UNIQUE,
column2 datatype,
...
);
This ensures that each value in column1 is unique.
SQL Count Unique Values
To count the number of unique values in a column, you can use the COUNT DISTINCT function:
SELECT COUNT(DISTINCT column_name) FROM table_name;
For example:
SELECT COUNT(DISTINCT employee_id) FROM employees;
This retrieves the count of unique employee IDs in the 'employees' table.
SQL Update Values
To update existing values in a table, you can use the UPDATE statement:
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
For example:
UPDATE employees
SET salary = 55000
WHERE last_name = 'Doe';
This updates the salary for employees with the last name 'Doe' to 55000.
Enhance your SQL Skills with ProjectPro!
This recipe has covered the basics of entering single and multiple values into a table in SQL. Whether you're dealing with unique values, counting distinct values, or updating existing values, SQL provides a flexible set of tools to meet your database management needs. Learning to enter single and multiple values in a SQL table is a fundamental skill for any aspiring database professional. However, true proficiency comes not just from theoretical knowledge but from practical experience. By embracing real-world projects, you can solidify your understanding and enhance your SQL skills. ProjectPro emerges as the ideal companion on this learning journey, offering a comprehensive platform with a repository of over 270+ projects rooted in data science and big data. Elevate your SQL expertise with ProjectPro and empower yourself to tackle complex challenges in the dynamic realm of database management.
