merged-sql-files.
md 2025-05-22
[Link]
-- filepath: c:\Users\yusuf\OneDrive\Masaüstü\HKUNV\3.sınıf 2.dönem\learn-
sql\[Link]
USE okul;
CREATE TABLE ogrenciler (
ogrenci_id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
ogrenci_ad VARCHAR(50) NOT NULL,
ogrenci_soyad VARCHAR(50) NOT NULL,
ogrenci_cinsiyet ENUM('Erkek', 'Kadin') NOT NULL,
ogrenci_durum SET('Aktif', 'Mezun', 'Kayitli') NOT NULL
);
-- All changes in the table structure are made by using the ALTER TABLE command,
followed by a
-- keyword that produces the specific change you want to make.
-- Three options are available: ADD, MODIFY, and DROP.
-- ADD enables you to add a column,
-- EX: ALTER TABLE < TABLE NAME>
-- ADD ATTRIBUTE NAME DATA TYPE;
-- DROP enables you to drop column,
-- Ex: ALTER TABLE < TABLE NAME>
-- DROP ATTRIBUTE NAME ;
-- MODIFY enables you to change column characteristics
-- ALTER TABLE< TABLE NAME>
-- MODIFY ATTRIBUTE NAME DATATYPE
/*
ALTER TABLE ogrenciler ADD COLUMN ogrenci_no INT;
*/
/*
ALTER TABLE ogrenciler CHANGE COLUMN ogrenci_no ogrenci_eposta VARCHAR(120);
*/
/*
ALTER TABLE ogrenciler DROP COLUMN ogrenci_eposta;
*/
/*
ALTER TABLE ogrenciler RENAME TO ogrenci;
*/
SHOW TABLES;
DESCRIBE table_name;
/*CREATE TABLE ogrenciler (
1/8
[Link] 2025-05-22
ogrenci_id INT PRIMARY KEY AUTO_INCREMENT,
ogrenci_isim VARCHAR(75),
ogrenci_soyisim VARCHAR(50),
ogrenci_no INT,
ogrenci_email VARCHAR(150)
);*/
/*INSERT INTO ogrenciler (ogrenci_isim, ogrenci_soyisim, ogrenci_no,
ogrenci_email)
VALUES ("Yusuf", "Ad", 221504005, "ad@[Link]");*/
INSERT INTO ogrenciler (ogrenci_ad, ogrenci_soyad, ogrenci_cinsiyet,
ogrenci_durum)
VALUES
("Ahmet", "Yilmaz", "Erkek", "Aktif"),
("Ayse", "Kaya", "Kadin", "Mezun"),
("Mehmet", "Demir", "Erkek", "Kayitli"),
("Fatma", "Sahin", "Kadin", "Aktif"),
("Ali", "Celik", "Erkek", "Mezun");
UPDATE ogrenciler
SET ogrenci_isim = "İsa Mehmet"
WHERE ogrenci_id = 1;
DELETE FROM ogrenciler WHERE ogrenci_id = 1;
/*CREATE TABLE writers (
writer_id INT AUTO_INCREMENT PRIMARY KEY,
writer_name VARCHAR(100),
writer_surname VARCHAR(100)
);*/
CREATE TABLE books (
book_id INT AUTO_INCREMENT PRIMARY KEY,
book_title VARCHAR(150),
writer_id INT,
release_date INT,
FOREIGN KEY (writer_id) REFERENCES writers(writer_id)
);
USE library;
/*CREATE TABLE writers (
writer_id INT AUTO_INCREMENT PRIMARY KEY,
writer_name VARCHAR(100),
writer_surname VARCHAR(100)
);*/
/*CREATE TABLE books (
book_id INT AUTO_INCREMENT PRIMARY KEY,
book_title VARCHAR(150),
writer_id INT,
2/8
[Link] 2025-05-22
release_date INT,
FOREIGN KEY (writer_id) REFERENCES writers(writer_id)
);*/
/*
INSERT INTO writers (writer_name, writer_surname)
VALUES
("Richard", "Dawkins"),
("Yuval Noah", "Harari"),
("Stephen", "King"),
("J.R.R.", "Tolkien"),
("Jane", "Austen"),
("George", "Orwell");*/
/*INSERT INTO books (book_title, writer_id, release_date)
VALUES
("LOTR", 4, 1949),
("Sapiens", 2, 2011),
("IT", 3, 1986),
("Pride and Prejudice", 5, 1813),
("1984", 6, 1949),
("The Selfish Gene", 1, 1976);*/
SELECT books.book_title, writers.writer_name, writers.writer_surname,
books.release_date
FROM books
INNER JOIN writers ON books.writer_id = writers.writer_id;
create TABLE EMPLOYEE (
EMP_NUM NUMERIC(4) NOT NULL UNIQUE,
EMP_TITLE VARCHAR(4),
EMP_LNAME VARCHAR(15) NOT NULL,
EMP_FNAME VARCHAR(15) NOT NULL,
EMP_DOB DATE NOT NULL,
EMP_HIRE_DATE DATE,
EMP_AREA_CODE VARCHAR(4) NOT NULL,
EMP_PHONE VARCHAR(12) NOT NULL,
PARK_CODE VARCHAR(10),
PRIMARY KEY(EMP_NUM),
CONSTRAINT FK_EMP_PARK FOREIGN KEY(PARK_CODE) REFERENCES THEMEPARK(PARK_CODE)
);
-- A JOIN clause is used to combine rows from two or more tables, based on a
related column between them.
-- (INNER) JOIN: Returns records that have matching values in both tables
-- LEFT (OUTER) JOIN: Returns all records from the left table, and the matched
records from the right table
-- RIGHT (OUTER) JOIN: Returns all records from the right table, and the matched
records from the left table
-- FULL (OUTER) JOIN: Returns all records when there is a match in either left or
right table
[Link]
3/8
[Link] 2025-05-22
-- filepath: c:\Users\yusuf\OneDrive\Masaüstü\HKUNV\3.sınıf 2.dönem\learn-sql\01-
[Link]
CREATE TABLE Customers (
CustomerID int NOT NULL,
CustomerName varchar(255) NOT NULL,
ContactName varchar(255),
Address varchar(255),
City varchar(255),
PostalCode varchar(50),
Country varchar(50),
PRIMARY KEY (CustomerID)
);
SELECT * FROM Customers;
-- The SELECT DISTINCT statement is used to return only distinct (different)
values.
SELECT DISTINCT Country FROM Customers;
SELECT COUNT(DISTINCT Country) FROM Customers;
-- Insert values into Customers table
INSERT INTO Customers (CustomerID, CustomerName, ContactName, Address, City,
PostalCode, Country)
VALUES
(1, 'Alfreds Futterkiste', 'Maria Anders', 'Obere Str. 57', 'Berlin', '12209',
'Germany'),
(2, 'Ana Trujillo', 'Ana Trujillo', 'Avda. Constitución 2222', 'México D.F.',
'05021', 'Mexico'),
(3, 'Antonio Moreno', 'Antonio Moreno', 'Mataderos 2312', 'México D.F.',
'05023', 'Mexico'),
(4, 'Around the Horn', 'Thomas Hardy', '120 Hanover Sq.', 'London', 'WA1 1DP',
'UK'),
(5, 'Berglunds snabbköp', 'Christina Berglund', 'Berguvsvägen 8', 'Luleå', 'S-
958 22', 'Sweden');
SELECT * FROM Customers
WHERE Country = 'Mexico';
-- The ORDER BY keyword is used to sort the result-set in ascending or descending
order.
-- DEFAIULT: ASC
SELECT * FROM Customers
ORDER BY Country DESC, CustomerName ASC;
SELECT * FROM Customers
ORDER BY City DESC;
SELECT * FROM Customers
WHERE Country = 'Mexico' AND CustomerName LIKE '%A';
4/8
[Link] 2025-05-22
SELECT * FROM Customers
WHERE Country = 'Mexico' OR City = 'Berlin';
SELECT * FROM Customers
WHERE NOT Country = 'Spain';
SELECT * FROM Customers
WHERE CustomerName NOT LIKE 'A%';
SELECT * FROM Customers
WHERE CustomerID NOT BETWEEN 10 AND 60;
SELECT * FROM Customers
WHERE City NOT IN ('Paris', 'London');
-- INSERT INTO Syntax
-- It is possible to write the INSERT INTO statement in two ways:
-- 1. Specify both the column names and the values to be inserted:
INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);
-- 2. If you are adding values for all the columns of the table, you do not need
to specify the column names in the SQL query. However, make sure the order of the
values is in the same order as the columns in the table. Here, the INSERT INTO
syntax would be as follows:
INSERT INTO table_name
VALUES (value1, value2, value3, ...);
INSERT INTO Customers (CustomerID, CustomerName, ContactName, Address, City,
PostalCode, Country)
VALUES (6, 'Blauer See Delikatessen', 'Hanna Moos', 'Forsterstr. 57', 'Mannheim',
'68306', 'Germany');
INSERT INTO Customers
Values (7, 'Blondel père et fils', 'Frédérique Citeaux', '24, place Kléber',
'Strasbourg', '67000', 'France');
INSERT INTO Customers (CustomerName, City, Country)
VALUES ('Cardinal', 'Stavanger', 'Norway');
INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode,
Country)
VALUES
('Cardinal', 'Tom B. Erichsen', 'Skagen 21', 'Stavanger', '4006', 'Norway'),
('Greasy Burger', 'Per Olsen', 'Gateveien 15', 'Sandnes', '4306', 'Norway'),
('Tasty Tee', 'Finn Egan', 'Streetroad 19B', 'Liverpool', 'L1 0AA', 'UK');
SELECT *
FROM Customers
WHERE Country IS NOT NULL;
5/8
[Link] 2025-05-22
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
-- Note: Be careful when updating records in a table! Notice the WHERE clause in
the UPDATE statement. The WHERE clause specifies which record(s) that should be
updated. If you omit the WHERE clause, all records in the table will be updated!
UPDATE Customers
SET ContactName = 'Alfred Schmidt', City = 'Hamburg'
WHERE CustomerID = 1;
UPDATE Customers
SET City = 'Oslo';
UPDATE
Customers
SET
City = 'Oslo',
Country = 'Norway'
WHERE CustomerID = 32;
DELETE FROM table_name WHERE condition;
-- Note: Be careful when deleting records in a table! Notice the WHERE clause in
the DELETE statement. The WHERE clause specifies which record(s) should be
deleted. If you omit the WHERE clause, all records in the table will be deleted!
DELETE FROM Customers WHERE CustomerName='Alfreds Futterkiste';
-- The following SQL statement deletes all rows in the "Customers" table, without
deleting the table:
DELETE FROM table_name;
-- Remove the Customers table:
DROP TABLE Customers;
DELETE FROM
Customers
WHERE
Country = 'Norway';
SELECT TOP 3 * FROM Customers;
-- 1. SQL Server / MS Access Syntax:
SELECT TOP number|percent column_name(s)
FROM table_name
6/8
[Link] 2025-05-22
WHERE condition;
-- MySQL Syntax:
SELECT column_name(s)
FROM table_name
WHERE condition
LIMIT number;
SELECT * FROM Customers
LIMIT 3;
SELECT TOP 50 PERCENT * FROM Customers;
SELECT TOP 3 * FROM Customers
WHERE Country='Germany';
SELECT * FROM Customers
ORDER BY CustomerName DESC
LIMIT 3;
-- SQL Aggregate Functions
-- An aggregate function is a function that performs a calculation on a set of
values, and returns a single value.
-- Aggregate functions are often used with the GROUP BY clause of the SELECT
statement. The GROUP BY clause splits the result-set into groups of values and the
aggregate function can be used to return a single value for each group.
-- The most commonly used SQL aggregate functions are:
-- MIN() - returns the smallest value within the selected column
-- MAX() - returns the largest value within the selected column
SELECT MIN(Price)
FROM Products;
SELECT MAX(column_name)
FROM table_name
WHERE condition;
SELECT MIN(Price) AS SmallestPrice
FROM Products;
-- COUNT() - returns the number of rows in a set
-- SUM() - returns the total sum of a numerical column
-- AVG() - returns the average value of a numerical column
-- Aggregate functions ignore null values (except for COUNT()).
SELECT MIN(Price) AS SmallestPrice, CategoryID
FROM Products
GROUP BY CategoryID;
SELECT COUNT(*)
7/8
[Link] 2025-05-22
FROM Products;
SELECT COUNT(column_name)
FROM table_name
WHERE condition;
SELECT COUNT(ProductID)
FROM Products
WHERE Price > 20;
SELECT COUNT(DISTINCT Price)
FROM Products;
SELECT SUM(Quantity)
FROM OrderDetails
WHERE ProductId = 11;
-- AS is Optional
-- Actually, in most database languages, you can skip the AS keyword and get the
same result:
-- Example
SELECT CustomerID AS ID
FROM Customers;
SELECT CustomerID ID
FROM Customers;
SELECT CustomerName, CONCAT(Address,', ',PostalCode,', ',City,', ',Country) AS
Address
FROM Customers;
8/8