Ichma-ich so’rovlar Oquv jarayoni Obyektlari

Ichma-ich so’rovlar
Oquv_jarayoni Obyektlari

1. 
   Employee(Xodim)
   
2. 
   Student
   
3. 
   Sciences(Fanlar)
   
4. 
   Groups(Gruppa)
   
5. 
   Lesson_process(Dars_jarayoni)
   

1. 
   CREATE TABLE [dbo].[Employee](
   

[Id] [int] NOT NULL,
[name] [nchar](50) NULL,
[adress] [nvarchar](50) NULL,
[data] [date] NULL,
CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

2. 
   CREATE TABLE Student
   

(
Id INT primary key identity(1,1),
Age INT,
FirstName NVARCHAR(20),
LastName NVARCHAR(20),
Email VARCHAR(30),
Phone VARCHAR(20)
)

3. 
   CREATE TABLE Sciences(Fanlar)
   

(
Id INT primary key identity(1,1),
name NVARCHAR(20),
)

4. 
   CREATE TABLE Groups
   

(
Id INT primary key identity,
name NVARCHAR(20),
)

5. 
   alter table Student
   

add Groups_Id int

6. 
   ALTER TABLE Student
   

ADD FOREIGN KEY(Groups_Id) REFERENCES Groups(Id);

7. 
   ALTER TABLE Customers
   

DROP COLUMN Address;

8. 
   ALTER TABLE Customers
   

ALTER COLUMN FirstName NVARCHAR(200);

9. 
   DROP TABLE Customers
   

10. 
    ALTER TABLE Orders
    

ADD PRIMARY KEY (Id);

11. 
    ALTER TABLE Orders
    

DROP FK_Orders_To_Customers;

12. 
    insert into Groups(name)
    

Values( 'Onatili')

13. 
    INSERT INTO Products
    

VALUES
('iPhone 6', 'Apple', 3, 36000),
('Galaxy S8', 'Samsung', 2, 46000),
('Galaxy S8 Plus', 'Samsung', 1, 56000)

1. 
   select * from Employees (Employees jadvalini chiqarish)
   

2. 
   select EmployeeID as emp from Employees (Employees jadvalidan EmployeeId ustunini emp ustuni korinishida chqarish)
   

3. 
   select EmployeeID as emp, LastName as Name from Employees where Country = 'UK'
   

4. 
   select count(*) Name from Employees
   

select count(LastName) from Employees

5. 
   select LastName, City from Employees
   

where City = 'London'
order by LastName(DESC, )

6. 
   select count (EmployeeID) from Employees
   

group by City

7. 
   select LastName+ ' ' + FirstName from Employees
   

8. 
   select LastName+ ' ' + FirstName from Employees
   

where FirstName like 'A%'

9. 
   select LastName+ ' ' + FirstName from Employees
   

where FirstName like '_A%'

10. 
    select City, LastName+ ' ' + FirstName from Employees
    

where City like 'London' or LastName like 'King '

11. 
    select City, LastName+ ' ' + FirstName from Employees
    

where City like 'London' and LastName like 'King '

12. 
    select Distinct City from Employees
    

13. 
    select Top 10 * from Employees order by City
    

14. 
    select LastName as name, Region,
    

CASE when Region is null then 'yoq'
else Region end as 'hudud'
from Employees

15. 
    select o.OrderID as id,
    

o.OrderDate as 'buyurtma sanasi',
c.CompanyName as 'mijoz'
from Orders as o
join Customers as c
on o.CustomerID=c.CustomerID
order by id desc

16. 
    select ord.OrderID as 'OrdID',
    

ord.OrderDate as 'Buyurtma vaqti',
ord.CustomerID as 'mijozID', cus.CustomerID as 'Mijoz ID'
from Orders as ord
left join Customers as cus
on ord.CustomerID=cus.CustomerID

17. 
    select ord.OrderID as 'OrdID',
    

ord.OrderDate as 'Buyurtma vaqti',
ord.CustomerID as 'mijozID', cus.CustomerID as 'Mijoz ID'
from Orders as ord
right join Customers as cus
on ord.CustomerID=cus.CustomerID

18. 
    select ord.OrderID as 'OrdID',
    

ord.OrderDate as 'Buyurtma vaqti',
ord.CustomerID as 'mijozID', cus.CustomerID as 'Mijoz ID'
from Orders as ord
left join Customers as cus
on ord.CustomerID=cus.CustomerID
union
select ord.OrderID as 'OrdID',
ord.OrderDate as 'Buyurtma vaqti',
ord.CustomerID as 'mijozID', cus.CustomerID as 'Mijoz ID'
from Orders as ord
right join Customers as cus
on ord.CustomerID=cus.CustomerID

19. 
    select ord.OrderID as 'OrdID',
    

ord.OrderDate as 'Buyurtma vaqti',
ord.CustomerID as 'mijozID', cus.CustomerID as 'Mijoz ID'
from Orders as ord
left join Customers as cus
on ord.CustomerID=cus.CustomerID
union all
select ord.OrderID as 'OrdID',
ord.OrderDate as 'Buyurtma vaqti',
ord.CustomerID as 'mijozID', cus.CustomerID as 'Mijoz ID'
from Orders as ord
right join Customers as cus
on ord.CustomerID=cus.CustomerID

20.