To add a column to an existing table using ALTER TABLE: Copy ALTER TABLE table_nameADD new_column_name data_type The Example Suppose that you have a table called products. The ‘products’ table currently contains the following columns and data: product_id product_name 1 Desktop Computer 2 Laptop 3 Tablet 4 Monitor 5 Printer The ultimate goal is to add … Read more
You can use the following syntax in order to apply UNION and UNION ALL using SQL: (1) Apply UNION while removing duplicates: Copy SELECT column_1, column_2,… FROM table_1 UNION SELECT column_1, column_2,… FROM table_2 (2) Apply UNION ALL while keeping the duplicates: Copy SELECT column_1, column_2,… FROM table_1 UNION ALL SELECT column_1, column_2,… FROM table_2 … Read more
To join tables from two different databases in SQL Server (under the same server): Copy SELECT tb_1.*,tb_2.*FROM [database_1].[table_schema].[table_name_1] tb_1JOIN [database_2].[table_schema].[table_name_2] tb_2 ON tb_1.id = tb_2.id The Steps Step 1: Create the first database and table To start, create the first database called database_1: Copy CREATE DATABASE database_1 Next, create a table called ‘products‘ (with a … Read more
Here are two approaches to create a temporary table in SQL Server: (1) The SELECT INTO approach: Copy SELECT column_1, column_2, column_3,… INTO #name_of_temp_table FROM table_name WHERE condition (2) The CREATE TABLE approach: Copy CREATE TABLE #name_of_temp_table ( column_1 datatype, column_2 datatype, column_3 datatype, . . column_n datatype ) Note that once you created the … Read more
To find the max value using SQL: Copy SELECT MAX(field_name) AS new_field_name FROM table_name Next, you’ll see how to find the max value across the following 3 scenarios: Under a single field After joining tables After using group by (1) Find the Max Value Under a Single Field To start with a simple example, let’s … Read more
You can use a LEFT JOIN to get all the records from the left table, and the records from the right table where there is a match (if there is no match, you’ll get NULL under the selected fields from the right table): Copy SELECT tb1.*,tb2.*FROM left_table_name tb1LEFT JOIN right_table_name tb2 ON tb1.id = tb2.id … Read more
To update records using SQL: Copy UPDATE table_nameSET column_1 = value_1, column_2 = value_2, …WHERE condition The Example Suppose that you created a table, where: The ‘product’ table also contains the following 6 records: product_id product_name price 1 Computer 800 2 TV 1200 3 Printer 150 4 Desk 400 5 Chair 120 6 Tablet 300 … Read more
Here are two ways to delete records from a table using SQL: (1) Delete records based on specified conditions: Copy DELETE FROM table_nameWHERE condition (2) Delete all the records in a given table: Copy DELETE FROM table_name The Example Suppose that you created a table, where: The ‘product’ table also contains the following 6 records: … Read more
To insert records into a table using SQL: Copy INSERT INTO table_name (column_1, column_2, column_3,…)VALUES(‘value_1’, ‘value_2’, ‘value_3’, …) The Example Assume that you created an empty table called the ‘product‘ table which contains 3 columns: product_id, product_name, and price: Copy CREATE TABLE product ( product_id int primary key, product_name nvarchar(50), price int) The ultimate goal is … Read more
You can use the following query to get the data type of columns in SQL Server: Copy SELECT TABLE_CATALOG,TABLE_SCHEMA,TABLE_NAME, COLUMN_NAME, DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS Next, you’ll see 3 scenarios to get the data type: 3 Scenarios to Get the Data Type of Columns in SQL Server Scenario 1 – Get the data type of all columns … Read more
