Scribd
Upload
18 views2 pages

CarData SQL Queries

The document contains SQL queries for managing and analyzing a CarDatabase. It includes commands for creating the database, displaying data, using aggregate functions, and performing various types of joins. Additionally, it demonstrates how to split datasets into tables and utilize conditional and nested queries.

Uploaded by

adarshhalse45
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
18 views2 pages

CarData SQL Queries

The document contains SQL queries for managing and analyzing a CarDatabase. It includes commands for creating the database, displaying data, using aggregate functions, and performing various types of joins. Additionally, it demonstrates how to split datasets into tables and utilize conditional and nested queries.

Uploaded by

adarshhalse45
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 2

SQL Queries for CarData

CREATE DATABASE CarDatabase;


USE CarDatabase;

-- Display all data and count rows


SELECT * FROM CarData;
SELECT COUNT(*) AS TotalRows FROM CarData;

-- WHERE Clause Example


SELECT * FROM CarData WHERE Price > 20000;

-- Aggregate Functions
SELECT
SUM(Price) AS TotalPrice,
COUNT(*) AS TotalCars,
MIN(Price) AS MinPrice,
MAX(Price) AS MaxPrice,
AVG(Price) AS AvgPrice
FROM CarData;

-- GROUP BY Example
SELECT Manufacturer, COUNT(*) AS CarCount
FROM CarData
GROUP BY Manufacturer;

-- HAVING Clause Example


SELECT Manufacturer, COUNT(*) AS CarCount
FROM CarData
GROUP BY Manufacturer
HAVING COUNT(*) > 5;

-- ORDER BY Clause
SELECT * FROM CarData ORDER BY Price ASC;
SELECT * FROM CarData ORDER BY Price DESC;

-- Splitting Dataset into Two Tables


CREATE TABLE CarDetails (
CarID INT PRIMARY KEY,
Manufacturer VARCHAR(100),
Model VARCHAR(100),
Year INT
);
CREATE TABLE CarPricing (
CarID INT PRIMARY KEY,
Price DECIMAL(10,2),
Mileage INT,
FOREIGN KEY (CarID) REFERENCES CarDetails(CarID)
);

-- INNER JOIN Example


SELECT d.Manufacturer, d.Model, p.Price
FROM CarDetails d
INNER JOIN CarPricing p ON d.CarID = p.CarID;

-- LEFT JOIN Example


SELECT d.Manufacturer, d.Model, p.Price
FROM CarDetails d
LEFT JOIN CarPricing p ON d.CarID = p.CarID;

-- FULL OUTER JOIN Example


SELECT d.Manufacturer, d.Model, p.Price
FROM CarDetails d
FULL OUTER JOIN CarPricing p ON d.CarID = p.CarID;

-- UNION Operation
SELECT * FROM CarData1
UNION
SELECT * FROM CarData2;

-- Conditional, Logical, and Arithmetic Operations


SELECT *,
(Price * 1.1) AS IncreasedPrice,
CASE
WHEN Price > 30000 THEN 'Expensive'
ELSE 'Affordable'
END AS PriceCategory
FROM CarData
WHERE Year >= 2015 AND Mileage < 50000;

-- Nested Query
SELECT * FROM CarData
WHERE Price = (SELECT MAX(Price) FROM CarData);

You might also like