Bajardi: Jumayev Bekzod Tekshirdi: Jo’raboyev Foziljon 3-amaliy ish. Music nomli jadvalini yaratamiz

Muhammad Al-Xorazmiy nomidagi Toshkent Axborot Texnologiyalari Universiteti Kompyuter injiniringi fakulteti

Multimediali ma’lumotlar bazasidan

AMALIY ISH

Bajardi: Jumayev Bekzod
Tekshirdi: Jo’raboyev Foziljon
3-amaliy ish.


Music nomli jadvalini yaratamiz:
CREATE TABLE music (
id INT PRIMARY KEY AUTO_INCREMENT,
title VARCHAR(255) NOT NULL,
artist VARCHAR(255) NOT NULL,
album VARCHAR(255),
release_date DATE,
genre VARCHAR(255),
length TIME,
size INT,
file_path VARCHAR(255) NOT NULL
);


Music nomli jadvalni qiymatlar bilan to’ldiramiz:
INSERT INTO music (title, artist, album, release_date, genre, length, size, file_path)
VALUES
('Bohemian Rhapsody', 'Queen', 'A Night at the Opera', '1975-10-31', 'Rock', '00:05:55', 12771000, 'C:/Music/Queen/Bohemian_Rhapsody.mp3'),
('Stairway to Heaven', 'Led Zeppelin', 'Led Zeppelin IV', '1971-11-08', 'Rock', '00:08:02', 19586500, 'C:/Music/Led_Zeppelin/Stairway_to_Heaven.mp3'),
('Hotel California', 'Eagles', 'Hotel California', '1976-12-08', 'Rock', '00:06:30', 14640000, 'C:/Music/Eagles/Hotel_California.mp3'),
('Smells Like Teen Spirit', 'Nirvana', 'Nevermind', '1991-09-10', 'Grunge', '00:05:01', 11902000, 'C:/Music/Nirvana/Smells_Like_Teen_Spirit.mp3');


Jadval ustuniga qo’shimcha xususiyat qo’shish uchun:
ALTER TABLE music
ADD COLUMN rating INT DEFAULT 0;


Jadval ustuniga ma’lumotini o’zgartirish uchun:
UPDATE music
SET artist = 'The Beatles'
WHERE id = 1;


Ma’lumotlar ustuniga asoslangan tartibda saralash uchun:
SELECT * FROM music ORDER BY release_date;



Belgilangan ustun nomi yoki maydoni bo'yicha ma'lumotlar o'zaro yig'indisini hisoblash:
SELECT genre, SUM(size) FROM music GROUP BY genre;

Aggregat funktsiyalar bilan biriktirish:
SELECT genre, COUNT(*) AS total_songs, AVG(rating) AS avg_rating
FROM music
GROUP BY genre;

Average so’rovi bilan ishlash:
SELECT AVG(rating) FROM music;