Write, Run & Share SQL Server queries online using OneCompiler's SQL Server online editor for free. It's one of the robust, feature-rich online editors for SQL Server. Getting started with the OneCompiler's SQL Server editor is really simple and pretty fast. The editor shows sample boilerplate code when you choose language as 'SQL Server' and start writing queries to learn and test online without worrying about tedious process of installation.
Microsoft SQL Server is a relational database management system developed by Microsoft. It is one of the most popular enterprise database platforms, known for its reliability, security, and comprehensive feature set. SQL Server uses T-SQL (Transact-SQL), which extends standard SQL with procedural programming, local variables, and various support functions.
Creates a new table with specified columns, data types, and constraints. SQL Server supports various constraints like PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, and CHECK to enforce data integrity. You can also define default values and identity columns for auto-incrementing primary keys. The table structure forms the foundation of your database schema.
CREATE TABLE EMPLOYEE (
empId int PRIMARY KEY,
name varchar(50) NOT NULL,
dept varchar(20),
salary decimal(10,2),
hire_date date DEFAULT GETDATE()
);
Adds new rows to a table. You can insert single rows by specifying values for each column, or insert multiple rows in a single statement for better performance. Column names are optional if you provide values for all columns in the correct order. SQL Server also supports INSERT INTO...SELECT to insert data from query results.
INSERT INTO EMPLOYEE (empId, name, dept, salary) VALUES (1, 'Clark', 'Sales', 50000);
INSERT INTO EMPLOYEE (empId, name, dept, salary) VALUES (2, 'Dave', 'Accounting', 45000);
INSERT INTO EMPLOYEE (empId, name, dept, salary) VALUES (3, 'Ava', 'Sales', 55000);
-- Insert multiple rows
INSERT INTO EMPLOYEE (empId, name, dept, salary) VALUES
(4, 'Bob', 'Engineering', 60000),
(5, 'Eve', 'HR', 48000);
Retrieves data from one or more tables. SQL Server provides powerful querying capabilities including filtering with WHERE, sorting with ORDER BY, and limiting results with TOP. You can select specific columns or use * for all columns. The SELECT statement is the foundation of data retrieval and can be combined with joins, subqueries, and aggregate functions.
-- Select all
SELECT * FROM EMPLOYEE;
-- Select with condition
SELECT * FROM EMPLOYEE WHERE dept = 'Sales';
-- Select specific columns with ordering
SELECT name, salary FROM EMPLOYEE ORDER BY salary DESC;
-- Limit results
SELECT TOP 10 * FROM EMPLOYEE;
-- Select with multiple conditions
SELECT * FROM EMPLOYEE WHERE dept = 'Sales' AND salary > 50000;
Modifies existing data in a table. Always use a WHERE clause to specify which rows to update, otherwise all rows will be modified. SQL Server supports updating multiple columns in a single statement and can use subqueries or joins to determine the new values. You can also use the OUTPUT clause to return the modified rows.
-- Update single column
UPDATE EMPLOYEE SET dept = 'Marketing' WHERE empId = 1;
-- Update multiple columns
UPDATE EMPLOYEE SET dept = 'Sales', salary = 52000 WHERE empId = 2;
-- Update with calculation
UPDATE EMPLOYEE SET salary = salary * 1.10 WHERE dept = 'Engineering';
Removes rows from a table. Always use a WHERE clause to specify which rows to delete, otherwise all rows will be removed. For removing all data while keeping the table structure, TRUNCATE TABLE is faster than DELETE without WHERE. SQL Server also supports DELETE with JOIN to remove rows based on conditions in related tables.
-- Delete specific rows
DELETE FROM EMPLOYEE WHERE empId = 1;
-- Delete with multiple conditions
DELETE FROM EMPLOYEE WHERE dept = 'Sales' AND salary < 45000;
-- Delete all rows (TRUNCATE is faster for this)
TRUNCATE TABLE EMPLOYEE;
Combines rows from two or more tables based on related columns. INNER JOIN returns only matching rows, LEFT JOIN returns all rows from the left table plus matches, RIGHT JOIN returns all rows from the right table plus matches, and FULL JOIN returns all rows from both tables. Joins are essential for querying normalized relational databases.
CREATE TABLE departments (dept_id int PRIMARY KEY, dept_name varchar(20));
CREATE TABLE employees (emp_id int PRIMARY KEY, name varchar(20), dept_id int);
INSERT INTO departments VALUES (1, 'Sales'), (2, 'Engineering'), (3, 'HR');
INSERT INTO employees VALUES (1, 'Alice', 1), (2, 'Bob', 2), (3, 'Charlie', 1);
-- Inner join
SELECT e.name, d.dept_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id;
-- Left join (includes employees without departments)
SELECT e.name, d.dept_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id;
SQL Server provides aggregate functions to perform calculations across rows. Common functions include COUNT, SUM, AVG, MIN, and MAX. Use GROUP BY to group rows and calculate aggregates for each group. The HAVING clause filters groups after aggregation, similar to how WHERE filters individual rows before aggregation.
CREATE TABLE sales (
id int,
product varchar(50),
amount decimal(10,2),
quantity int
);
INSERT INTO sales VALUES (1, 'Laptop', 999.99, 2), (2, 'Mouse', 29.99, 10),
(3, 'Laptop', 999.99, 1), (4, 'Keyboard', 79.99, 5);
-- Group by with aggregates
SELECT product,
COUNT(*) as orders,
SUM(quantity) as total_qty,
SUM(amount * quantity) as revenue
FROM sales
GROUP BY product
ORDER BY revenue DESC;
-- Having clause
SELECT product, SUM(quantity) as total_qty
FROM sales
GROUP BY product
HAVING SUM(quantity) > 3;
A subquery is a query nested inside another query. Subqueries can be used in SELECT, FROM, WHERE, and HAVING clauses. They are useful for breaking complex queries into logical steps, filtering based on aggregated data, or comparing values against a computed result. SQL Server supports correlated subqueries that reference columns from the outer query.
-- Subquery in WHERE clause
SELECT * FROM EMPLOYEE
WHERE salary > (SELECT AVG(salary) FROM EMPLOYEE);
-- Subquery in FROM clause
SELECT dept, avg_salary
FROM (
SELECT dept, AVG(salary) as avg_salary
FROM EMPLOYEE
GROUP BY dept
) AS dept_averages
WHERE avg_salary > 50000;
The CASE expression provides conditional logic in SQL queries, similar to if-else statements in programming languages. Use CASE to create computed columns, categorize data, or handle different conditions in your results. CASE can be used in SELECT, WHERE, ORDER BY, and other clauses wherever an expression is valid.
SELECT name, salary,
CASE
WHEN salary >= 60000 THEN 'Senior'
WHEN salary >= 45000 THEN 'Mid-Level'
ELSE 'Junior'
END AS level
FROM EMPLOYEE;
-- CASE in ORDER BY
SELECT * FROM EMPLOYEE
ORDER BY
CASE dept
WHEN 'Engineering' THEN 1
WHEN 'Sales' THEN 2
ELSE 3
END;
SQL Server provides a comprehensive set of string functions for text manipulation and analysis. Common functions include LEN for length, UPPER/LOWER for case conversion, SUBSTRING for extracting parts, CONCAT for combining strings, and TRIM for removing whitespace. These functions are useful for data cleaning, formatting, and text-based searching.
SELECT
UPPER('hello') AS uppercase,
LOWER('HELLO') AS lowercase,
LEN('hello') AS length,
CONCAT('Hello', ' ', 'World') AS combined,
SUBSTRING('Hello World', 1, 5) AS sub,
REPLACE('Hello World', 'World', 'SQL') AS replaced,
TRIM(' hello ') AS trimmed;
SQL Server has powerful date and time functions for working with temporal data. GETDATE() returns the current date and time, DATEADD adds intervals to dates, DATEDIFF calculates the difference between dates, and DATEPART extracts components like year, month, or day. These functions are essential for date-based filtering, calculations, and reporting.
SELECT
GETDATE() AS current_datetime,
CAST(GETDATE() AS DATE) AS current_date,
DATEADD(day, 7, GETDATE()) AS next_week,
DATEADD(month, -1, GETDATE()) AS last_month,
DATEDIFF(day, '2024-01-01', GETDATE()) AS days_since,
DATEPART(year, GETDATE()) AS current_year,
DATENAME(month, GETDATE()) AS month_name;
A view is a virtual table based on a SELECT query. Views simplify complex queries by encapsulating them behind a simple name, provide a security layer by restricting access to specific columns or rows, and present data in a different structure than the base tables. Views don't store data themselves but execute the underlying query when accessed.
-- Create a view
CREATE VIEW employee_summary AS
SELECT dept, COUNT(*) as emp_count, AVG(salary) as avg_salary
FROM EMPLOYEE
GROUP BY dept;
-- Query the view
SELECT * FROM employee_summary WHERE emp_count > 1;
ALTER TABLE modifies an existing table structure by adding, removing, or modifying columns and constraints. DROP TABLE removes the table and all its data permanently. These DDL (Data Definition Language) commands are essential for database schema evolution and maintenance. Always backup data before making structural changes in production.
-- Add column
ALTER TABLE EMPLOYEE ADD email varchar(100);
-- Modify column
ALTER TABLE EMPLOYEE ALTER COLUMN name varchar(100);
-- Drop column
ALTER TABLE EMPLOYEE DROP COLUMN email;
-- Drop table
DROP TABLE IF EXISTS EMPLOYEE;