OMIS 105 Midterm 1

metadata vs data

Metadata describes the table (name VARCHAR, age int, salary int).

Data fills the table.

database

A structured collection of data (Organized, Persistent, Queryable)

DBMS

-software that sits between applications and data.

-store data, retrieve data, ensure consistency, handle multiple users

create table

CREATE TABLE products (
    product_id INTEGER,
    product_name VARCHAR,
    price INTEGER
);

Insert into table

INSERT INTO products
VALUES
(1, 'Laptop', 1000),
(2, 'Phone-12', 800),
(3, 'Tablet-1', 500);

show contents of table

SELECT * FROM products;

or SHOW TABLES;

data types & constraints for building tables

CREATE TABLE products (
    product_id INTEGER PRIMARY KEY,
    name       VARCHAR NOT NULL,
    price      DECIMAL(10,2) CHECK (price > 0),
    category   VARCHAR DEFAULT 'Other'
);

• INTEGER, VARCHAR, DECIMAL = data types

• PRIMARY KEY, NOT NULL, CHECK, DEFAULT = constraints

SQL Order of Operations

SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY → LIMIT

SELECT category, COUNT(*) AS total
FROM products
WHERE price > 100
GROUP BY category
HAVING COUNT(*) > 2
ORDER BY total DESC
LIMIT 5;

show unique categories & count unique categories

SELECT DISTINCT category
FROM products;

SELECT COUNT(DISTINCT category) AS num_categories
FROM products;

LIKE FUNCTION

LIKE

Use when: You want to find text that matches a pattern.

Pattern	Meaning	Example
'S%'	Starts with S	'Shoes'
'%Pro%'	Contains Pro	'Laptop Pro'
'%e'	Ends with e	'Phone'
'___'	Exactly 3 characters	'Bag'
'S___'	S + exactly 3 characters	'Shoe'

SELECT *
FROM products
WHERE product_name LIKE 'S%';

Remember: % = any number of characters. _ = exactly one character.

round a number to 2 decimal places.

ROUND(price, 2) = 2 decimal places

Schema

the structure or blueprint of a database.

primary key def & 2 rules

A unique identifier for every row in a table.

unique, not null

update table

UPDATE students 
SET age = 21, country = 'Canada' 
WHERE id = 1;

delete row from table

DELETE FROM employees
WHERE emp_id = 6;