1-bob. Ma`lumotlar bazasining asosiy tushunchalari

SELECT *

FROM Subject

ORDER BY semester, subj_name;

ORDER BY semester SQL tili 1 ta so’rov ichiga 2-so’rovni joylashtirib ishlatish imkonini beradi.

Misol: birorta talabani familiyasi bo’yicha uning ID sini topish talab etilsa, va bu talabani barcha baholari haqidagi ma‘lumotni ko’rmoqchi bo’lsak, quyidagi so’rovni yozish mumkin.

SELECT*

FROM Exam_marks WHERE student_ID =( SELECT student_ID FROM student

WHERE surname= ‘Petrov’

)

Tasvirlar

Tasvir. Tasvir bir yoki bir nеchta doimiy jadvallar asosida yaratilgan virtual jadvaldir.
Tasvirlar quyidagi komanda orqali yaratiladi:

CREATE VIEW AS

Misol:

CREATE VIEW info_order

AS SELECT onum, amt, cname FROM orders, customers

WHERE orders.cnum = customers.cnum;

Tasvirlarga murojaat. Tasvirga SQL komandalarini masalan SELECT komandasini qo‘llash mumkin.

Masalan:

SELECT * FROM info_order;
Tasvirga so‘rov asosida yangi tasvir yaratilishi mumkin. Lеkin bu imkoniyatdan amaliyotda kam foydalaniladi.


Joylashtirilgan so‘rovli tasvir. Misol. Eng katta rеytingga ega oluvchilarni ko‘rsatuvchi Cust_Rating_Max nomli tasvir yarating:

CREATE VIEW Cust_Rating_Max AS SELECT cname, city, rating FROM Customers

WHERE rating = (SELECT Max(rating) FROM Customers);


Guruhlanuvchi tasvir. Misol. Har bir sotuvchi uchun buyurtmalar o‘rta narxini ko‘rsatuvchi Sale_Amt_Avg nomli tasvir yarating:

CREATE VIEW Sale_Amt_Avg AS SELECT sname, AVG(amt) FROM Salespeople, Orders

WHERE Salespeople.snum = Orders.snum GROUP BY sname;