Neondb companies table

Create Table and Update

CREATE DATABASE IF NOT EXISTS Interview;

SHOW DATABASES;

// Create table

CREATE TABLE companies (
    id SERIAL PRIMARY KEY,
    site_url TEXT,
    company_name TEXT NOT NULL,
    company_size INTEGER,
    company_domain TEXT,
    emails TEXT,
    linkedin TEXT,
    mobile_number TEXT
);

CONSTRAINTS
PRIMARY KEY → Ensures each row is unique & not null
FOREIGN KEY → Links one table to another
UNIQUE → Ensures no duplicate values in a column
NOT NULL → Ensures a column must have a value
CHECK → Ensures column values meet a condition
DEFAULT → Provides default value if none given

SHOW TABLES;
SELECT * FROM companies;

INSERT INTO companies (site_url, company_name, company_size, company_domain, emails)
VALUES
('https://alp.consulting.com', 'ALP Consulting', 50, 'Consulting Services', NULL),
('https://www.accenture.com', 'Accenture', 791000, 'Professional Services', '[email protected]');

# Note: You don’t need to insert into id because it auto-increments.

UPDATE companies
SET company_size = 800000, emails = '[email protected]'
WHERE company_name = 'Accenture';

DELETE FROM companies
WHERE id = 2;

# add new column
ALTER TABLE companies
ADD COLUMN headquarters TEXT;

ALTER TABLE companies
RENAME COLUMN company_domain TO industry_domain;

ALTER TABLE companies
ALTER COLUMN company_size TYPE BIGINT;

DROP: DROP TABLE companies;

Definition: Removes the entire table structure and its data from the database.

DELETE: DELETE FROM companies WHERE company_name = 'Accenture';

Definition: Deletes data row by row

TRUNCATE: TRUNCATE TABLE companies;

Definition: Removes all rows from a table, Table structure remains, but data is wiped out.

updating column type

ALTER TABLE "companies"
ALTER COLUMN "company_size"
SET DATA TYPE integer;

Select with multiple conditions

SELECT company_name
FROM companies
WHERE company_size >= 1000
AND company_domain = 'Financial Services / Banking';



SELECT company_name
FROM companies
WHERE company_domain IS NULL;

SELECT COUNT(company_name) AS total_companies
FROM companies
WHERE company_name IS NOT NULL;

One shot Query

SELECT 
    company_domain, 
    COUNT(*) AS total_companies, 
    AVG(company_size) AS avg_size
FROM companies
WHERE company_size > 1000          -- filter individual rows
GROUP BY company_domain             -- aggregate by domain
HAVING COUNT(*) >= 2                -- filter aggregated groups
ORDER BY avg_size DESC              -- sort by average company size
LIMIT 10;                           -- limit results

• SELECT → choose columns & aggregates

• WHERE → filter individual rows

• GROUP BY → aggregate by domain

• HAVING → filter aggregated groups

• ORDER BY → sort results

• LIMIT → restrict number of results

Select

LIKE In SQL, the LIKE operator is used for pattern matching in string columns.

use when you dont know the full data of column rows

SELECT company_name, company_domain 
FROM companies 
WHERE company_domain LIKE '%Financial%';

# NON CASE SENSITIVE
SELECT *
FROM companies
WHERE company_name ILIKE '%morgan%';

Order by

-- Sort by company size ascending
SELECT company_name, company_size 
FROM companies
ORDER BY company_size ASC;

-- Sort by company size descending
SELECT company_name, company_size 
FROM companies
ORDER BY company_size DESC;

LIMIT

LIMIT

Restrict number of rows returned.

SELECT company_name, company_size
FROM companies 
ORDER BY company_size DESC
LIMIT 10;

COUNT(*) AS total_companies - create new virtual column for query only

GROUP BY & HAVING

The GROUP BY clause in SQL is used to organize rows that have the same values in specified columns into summary rows.

• For each unique value, it creates a "group" of rows.

• eg. companies can be from same domain (unique domain)

• so we can group companies by unique domain

• eg banking repeating 3 times in one column (banking_domain)

Perform aggregation on data.

Aggreations: COUNT(*) AS total_companies

AVG(company_size) AS avg_size

common SQL aggregation functions:

• COUNT() — Counts the number of rows.

• SUM() — Adds up values in a column.

• AVG() — Calculates the average value.

• MIN() — Gets the smallest value.

• MAX() — Gets the largest value.

-- Total companies per domain
SELECT company_domain, COUNT(*) AS total_companies
FROM companies
GROUP BY company_domain;

-- Average company size per domain
SELECT company_domain, AVG(company_size) AS avg_size
FROM companies
GROUP BY company_domain;

HAVING

Filter on aggregated results. from group by

• HAVING filters groups after aggregation (using functions like COUNT, AVG, SUM).

SELECT company_domain, count(*) AS total_company
FROM companies
Group by company_domain
HAVING COUNT(*) > 2;

WHERE

The WHERE clause in SQL is used to filter rows based on specified conditions before any grouping or aggregation happens.

Using Multiple Conditions in WHERE

You can combine multiple conditions using logical operators:

• AND — All conditions must be true.

• OR — At least one condition must be true.

• NOT — Negates a condition.

SELECT *
FROM companies
WHERE company_size > 1000
  AND company_domain LIKE '%Financial%'
  AND company_name != 'ABC Corp';
  
SELECT *
FROM companies
WHERE company_size < 50
  OR company_domain = 'Healthcare';

# combined AND OR
SELECT *
FROM companies
WHERE (company_domain = 'Financial Services' OR company_domain = 'Banking')
  AND company_size > 500;

---------------------------------------------------