---- first lesson homework -- data is row information that is collected to be organized and analayzed to get insights for business. -- database is organized collection of data -- relation database is one that stores data in tables -- table is fundamental database object used to store and organize data within a relational database

--1 Define the following terms: data, database, relational database, and table.

--2List five key features of SQL Server.

--Relational database engine/High availability and disaster recovery/Advanced security/Data integration and Warehousing/ Big data integration.

--3 What are the different authentication modes available when connecting to SQL Server? (Give at least 2) --windows authentication/SQL server authentication

--- Medium mode ---

--4/5. create a new database in SSMS named SchoolIDB/ write and execute a query to create table called Students with columns: StudentID (INT, PRIMARY KEY), Name (VARCHAR(50)), Age (INT). --create database SchoolIDB --use SchoolIDB --drop table if exists students --create table Students (StudentID int primary key, Name varchar (50), age int ) --select * from Students

--6.Describe the differences between SQL Server, SSMS, and SQL -- SQL is a query language that helps users to connect with a databse. / SQL server - relational database management system, it is the backbone for data storage and processing. / SSMS > it provides a user_friendly environment for tasks such as database administration, queries, and scripting.

--- HARD --- --7. DQL > data query language. it is used only to fetch data from a database and include 'select' query -- DDL > data definition language. it is used to create and modify tables, views, users, and other objects in the database and includes queries - create, alter,table. -- DCL > data control language. it allows other people to qyery a table you created and include GRAND and REVOKE queries -- DML > data manipulation language. it is used on data itself and include INSERT, UPDSTR and DELETE queries -- TCL > transaction control language. that is used to manage transactio and in a relational database and include COMMIT and ROLLBACK queries.

--8. Write a query to insert three records into the Students table. --insert into Students values(1, 'Eldor', 19), (2, 'Elyor', 20), (3, 'Farrux', 23);

--9 Step1 - download AdventureWorksDW2022.bak file from :https://github.com/Microsoft/sql-server-samples/releases/download/adventureworks/AdventureWorksDW2022.bak -- Step2 - save this AdventureWorksDW2022.bak file to C:\ disk -- Step3 - open SQL -- Step4 - open "Databases" folder in Object Explorer -- Step5 - select Restore Database... -- Step6 - choose Device and click ... button, click Add, choose AdventureWorksDW2022.bak file and click ok -- Step7 - back to Object Explorer, click Refresh

--LESSON_2 --1.Create a table Employees with columns: EmpID INT, Name (VARCHAR(50)), and Salary (DECIMAL(10,2)). --drop table if exists Employees --create table Employees --( --EmpID int primary key, Name varchar (50), --Salary decimal (10,2)) --select * from Employees --2. Insert three records into the Employees table using different INSERT INTO approaches (single-row insert and multiple-row insert). --insert into Employees --values (1, 'john', 999.9), (2,'leo', 1200), (3, 'Trafford', 1100);

--3. Update the Salary of an employee to 7000 where EmpID = 1. --update Employees --set --salary=7000 --where EmpID =1 --4.Delete a record from the Employees table where EmpID = 2 --delete from Employees --WHERE EmpID=2

--5. Give a brief definition for difference between DELETE, TRUNCATE, and DROP --these three queries are used for removals. the drop query is used to delete tables and database completely while the truncate and delete quaries are mainly for removing rows and data in tables. As for the difference betwen delete and truncate, delete can remove rows and data by logging, making the process slower, and it can also use where, clouse, to remove specific data in tables. Compared to the delete query, the truncate query may work faster as it does not usually log while removing data, and using Where clouse with truncate quiery to specify data to remove is impossible.

--6 Modify the Name column in the Employees table to VARCHAR(100). --alter table Employees --alter column name varchar (100) --7 Add a new column Department (VARCHAR(50)) to the Employees table. --alter table Employees --add Department varchar (50)

--8. Change the data type of the Salary column to FLOAT. --alter table Employees --alter column salary float

--9 Create another table Departments with columns DepartmentID (INT, PRIMARY KEY) and DepartmentName (VARCHAR(50)). --create table Departments (DepartmentID int primary key, DepartmenName varchar(50)) --select * from Departments -- yangi table

--10 Remove all records from the Employees table without deleting its structure. --truncate Employees --select * from Employees -- eski table ni o'chirib structurasini shu holich qoldirayapman

--intermediate-level-tasks

--drop table if exists Departments --create table Departments (DepartmentID int primary key, DepartmenName varchar(50), floor_number int, salary int, short_code int); --select * from Departments ----11. nsert five records into the Departments table using --Insert into Departments --values (1, 'Finance', 3, 5200, 1234),(2, 'IT', 3, 6800, 1274), --(3, 'Management', 5, 1200, 1634), --(4, 'administration', 1, 800, 1334), --(5, 'HR', 4, 5500, 4234);

--12. Update the Department of all employees where Salary > 5000 to 'Management'.

-- biz bu taskda oyligi 5000 dan katta bo'lganlarni --departmentini Managementga o'zgartiring degani tufayli --yani rowni o'zgartirganimiz uchun update dan foydalanamiz

--update Departments --set --DepartmenName = 'management' --where salary > 5000

--13. Write a query that removes all employees but keeps the table structure intact --delete from Employees --14.Drop the Department column from the Employees table --alter table Employees --drop column Department

--15. Rename the Employees table to StaffMembers using SQL commands -- exec sp_rename 'Employees' , 'StaffMembers'
-- bu kod har qanday table ni nomini o'zgartirish uchun. --16 DROP TABLE DEPARTMENTS

--ADVANCED-LEVEL TASKS --17Create a table named Products with at least 5 columns, including: ProductID (Primary Key), --ProductName (VARCHAR), Category (VARCHAR), Price (DECIMAL) /// 18. Add a CHECK constraint to ensure Price is always greater than 0. --drop table if exists Products --create table Products (ProductID int primary key, ProductName varchar (50), category varchar (50), --price decimal constraint PRICE check (price>=0)) -- check constraint qo'shish --select * from Products

--19. Modify the table to add a StockQuantity column with a DEFAULT value of 50. --alter table Products --add StockQuantity int constraint df_stockquantity default 50; -- adding a new column with a default value

--20.Rename Category to ProductCategory --exec sp_rename 'Products.category', 'ProductCategory', 'column'; -- column nomini o'zgartirish.

--21 insert 5 records into the Products table using standard INSERT INTO queries. --insert into Products (ProductID, ProductName, ProductCategory, price) --values (3, 'Banana', 'fruits', 12000), --(4, 'fridge', 'household_appliance', 250), --(5, 'laptop', 'tech_tools', 600), --(6, 'smarphone', 'tech_tools', 350), --(7, 'cookies', 'cakes', 40000)

--22 Use SELECT INTO to create a backup table called Products_Backup containing all Products data.

--select * into Products_backup from Products

--23 Rename the Products table to Inventory. --exec sp_rename 'Products', 'Inventory' -- table nomini o'zgartirish yo'li. --select *from Inventory

--24 Alter the Inventory table to change the data type of Price from DECIMAL(10,2) to FLOAT. --alter table inventory --alter column price float -- bu kod dastlab ishlamadi chunki biz price column ni boshqa birnech object ga ulagan edik

--alter table inventory --drop constraint PRICE; --bu kod orqali ulangan object drop qilindi

--alter table inventory --add price decimal constraint PRICE check (price>=0); --- va yana qo'shildi

--25Add an IDENTITY column named ProductCode that starts from 1000 and increments by 5 to Inventory table. --alter table inventory --add ProductCode int identity (1000, 5)

---------LESSON 3 --------------

---EASY-LEVEL TASKS --1. Define and explain the purpose of BULK INSERT in SQL Server. --BULK INSERT - is a database operation designed to efficiently load large number of records into a database table in a single operation.

--2. List four file formats that can be imported into SQL Server. -- CSV, TSV, Excel, and XML files. --3 Create a table Products with columns: ProductID (INT, PRIMARY KEY), ProductName (VARCHAR(50)), Price (DECIMAL(10,2)). drop table if exists Products create table Products (ProductID int primary key, productName varchar (50), Price decimal(10,2)) --4 Insert three records into the Products table using INSERT INTO. insert into Products (ProductID, productName, Price) values (3, 'Banana', 12000), (7, 'cookies', 40000), (4, 'fridge', 250);;

--5 Explain the difference between NULL and NOT NULL. --null allows missing data while not null requires data to be present

--6 Add a UNIQUE constraint to the ProductName column in the Products table. alter table products add constraint uq_produtname unique(productName);

--7 a comment on unique constrint - it ensures that all values in a column or combination of columns --are unique within a database table

--8. Add CategoryID column to the Products table. alter table Products add CategoryID int select * from Products --9. Create a table Categories with a CategoryID as PRIMARY KEY and a CategoryName as UNIQUE. create table Categories (CategoryID int primary key, CategoryName varchar (50) unique) --10 Explain the purpose of the IDENTITY column in SQL Server. --it is used to automatically generate unique, sequential numeric values for a column

------------ MEDIUM-LEVEL TASKS ----------

SQL