Kami menganalisis database dan bahasa SQL. (Bagian 6 - Pengecekan tugas akhir) - "Proyek Java dari A sampai Z"

Artikel dari seri tentang membuat proyek Java (tautan ke materi lain ada di akhir). Tujuannya adalah untuk menganalisis teknologi utama, hasilnya adalah penulisan bot telegram. Bagian ini berisi tentang analisis tugas akhir pada database. Salam, para pembaca yang budiman. Hari ini kita akan menganalisis tugas dari artikel terakhir tentang database. Menarik karena ditujukan untuk wawancara di tingkat Menengah. Artinya, setelah tugas ini Anda sudah dapat mengikuti wawancara, dan Anda akan berhasil melewati setidaknya sebagian dari apa yang berkaitan dengan database relasional. Saya tahu betapa pentingnya artikel ini, dan oleh karena itu saya akan mengerahkan seluruh pengalaman saya untuk membuatnya bermanfaat dan menarik. Dan jika Anda tidak tertidur di tengah artikel, itu berarti saya mencapai tujuan saya. Saya tidak akan mengulangi seluruh tugas: Saya akan mengutipnya sebelum menyelesaikan setiap tugas, menggarisbawahinya dalam huruf miring. Saya berharap setiap orang yang membaca artikel ini akan menjalankan semua query di database mereka dan mendapatkan hal yang sama. Hal ini akan membawa keuntungan maksimal bagi bisnis. Dan saya akan sedikit lebih bahagia memikirkan bahwa saya membantu seseorang dalam tugas sulit kita)

Latihan 1

Tulis skrip SQL untuk membuat tabel 'Siswa' dengan kolom berikut: id (kunci utama), nama, nama belakang, email (unik). Kami sudah melakukan ini, jadi seharusnya tidak ada masalah. Dalam skrip, Anda perlu menentukan kunci utama dan bidang unik yang berbeda dari kunci utama. Pertama, mari buat database baru untuk tugas ini: $ CREATE DATABASE final_task; Dan mari kita gunakan database ini: $USE final_task; Setelah lingkungan diatur dan siap untuk menjalankan pekerjaan, kita dapat menulis skrip berikut: $ CREATE TABLE student ( id INT AUTO_INCREMENT, name VARCHAR(40), last_name VARCHAR(50), email VARCHAR(100), PRIMARY KEY ( id), UNIK (email ) ); Sejauh ini, tidak ada yang baru dibandingkan dengan apa yang telah kita lalui. Komentar apa pun tidak diperlukan, mari kita lanjutkan.

Tugas 2-3

Tulis skrip SQL untuk membuat tabel 'Buku' dengan kolom berikut: id, judul (id + judul = kunci utama). Tautkan 'Siswa' dan 'Buku' dengan hubungan 'Siswa' satu-ke-banyak 'Buku'. Mari gabungkan dua tugas menjadi satu agar lebih cepat dan nyaman. Cara menambahkan kunci asing terpisah sudah saya bahas di artikel sebelumnya. Sebagai tambahan, kita perlu mengingat bagaimana kita menjalin hubungan dan melalui apa. Artikel sebelumnya akan membantu anda, selanjutnya ini scriptnya: $ CREATE TABLE book ( id INT, title VARCHAR(100), student_id INT DEFAULT NULL, PRIMARY KEY (id, title), FOREIGN KEY (student_id) REFERENSI student (id ) ); Dengan cara sederhana ini, kami menambahkan kunci komposit untuk tabel kami PRIMARY KEY (id, title) , sekarang kuncinya akan sama persis dengan pasangannya. Artinya, dalam tabel bisa terdapat lebih dari satu nilai bidang id yang identik. Dan persis sama untuk judulnya.

Tugas 4

Tulis skrip SQL untuk membuat tabel 'Guru' dengan kolom berikut: id (kunci utama), nama, nama belakang, email (unik), subjek. Kami terus mempersiapkan database kami untuk query, membuat tabel guru: $ CREATE TABLE guru( id INT AUTO_INCREMENT, nama VARCHAR(30), nama belakang VARCHAR(30), email VARCHAR(100), subjek VARCHAR(40), PRIMARY KEY ( id), UNIK (email) ); Sejauh ini tidak sulit bukan? Tiga tugas sudah selesai!

Tugas 5

Hubungkan 'Siswa' dan 'Guru' dengan hubungan 'Siswa' banyak-ke-banyak Guru'. Sekarang ini lebih menarik! Kita baru saja membicarakan hal ini terakhir kali. Izinkan saya mengingatkan Anda apa yang diperlukan untuk mencapai hal ini: Anda perlu membuat tabel perantara yang akan menyimpan pasangan siswa-guru. Dengan bantuannya, dimungkinkan untuk menciptakan hubungan banyak-ke-banyak. Oleh karena itu, mari kita buat tabel Students_x_techers . Pendekatan penamaannya terbuka dan bisa juga: student_teacher . $ CREATE TABLE siswa_x_teachers ( student_id INT NOT NULL, teacher_id INT NOT NULL, PRIMARY KEY (student_id, teacher_id), FOREIGN KEY (student_id) REFERENSI student(id), FOREIGN KEY (teacher_id) REFERENSI teacher(id) ); Seperti yang Anda lihat, semuanya dilakukan dengan jelas dan koheren. Kami memiliki kunci komposit untuk dua kunci asing: student_id dan teacher_id. Mengapa juga kunci asing? Agar kita yakin bahwa catatan di tabel siswa dan guru ada untuk pasangan yang dicatat.

Tugas 6

Pilih 'Siswa' yang memiliki nama belakang 'oro', misalnya 'Sidorov', 'Voronovsky'. Agar menarik dan visual bagi kami, saya sarankan untuk menambahkan beberapa siswa terlebih dahulu, sehingga ada yang cocok untuk permintaan ini, dan ada yang tidak. Oleh karena itu, mari kita tuliskan siapa saja yang harus disertakan sebagai hasil permintaan: $ INSERT INTO student (name, last_name, email) VALUES ('Ivan', 'Sidorov', 'ivan.sidorov@gmail.com'); $ INSERT INTO student (nama, nama belakang, email) VALUES ('Nikolay', 'Voronovsky', 'nikolay.voronovsky@gmail.com'); Dan yang tidak boleh masuk: $ INSERT INTO student (name, last_name, email) VALUES ('Roman', 'Fortny', 'roman.fortny@gmail.com'); $ INSERT INTO student (nama, nama belakang, email) VALUES('Kostya', 'Petrov', 'kostya.petrov@gmail.com'); Mari kita cek hasilnya, lihat daftar data pada tabel siswa: $SELECT*FROM student; dan kita mendapatkan: Total ada empat catatan, dua di antaranya harus sesuai, dan dua tidak. Setelah menyiapkan semua data untuk permintaan tersebut, kita dapat membuat permintaan untuk tugas itu sendiri: $ SELECT * FROM student WHERE last_name LIKE '%oro%'; Hasilnya, Ivan dan Nikolai memeriksa daftarnya.

Tugas 7

Tugas selanjutnya, kita membaca: Pilih dari tabel 'Siswa' semua nama belakang ('nama_belakang') dan jumlah pengulangannya. Pertimbangkan bahwa ada nama yang sama dalam database. Urutkan berdasarkan kuantitas dalam urutan menurun. Seharusnya terlihat seperti ini:

nama keluarga	kuantitas
Petrov	15
Ivanov	12
Sidorov	3

Untuk kejelasan, Anda perlu menambahkan lebih banyak data. Tanpa basa-basi lagi, mari tambahkan Petrov, Ivanov, dan Sidorov, yang tidak mengetahui hubungan kekerabatan mereka ;) Saya tidak akan membuat alamat email, saya hanya akan mengecualikannya dari entri baru. Mari kita jalankan perintah berikut sebanyak 12 kali: $ INSERT INTO student (name, last_name) VALUES ('Ivan', 'Ivanov'); Mari tambahkan 15 Petrov: $ INSERT INTO student (name, last_name) VALUES ('Petr', 'Petrov'); Dan dua Sidorov (kami sudah memilikinya))): $ INSERT INTO student (name, last_name) VALUES ('Sidor', 'Sidorov'); Datanya sekarang sudah siap. Untuk mendapatkan data tersebut, Anda perlu melakukan pengelompokan; untuk melakukan pengelompokan, Anda perlu menggunakan operator Group By, dan Anda perlu melakukan ini dengan kolom last_name. Anda juga dapat memperhatikan bahwa jumlah pengulangan ditetapkan sebagai quantity , dan di sini Anda juga perlu mengingat cara membuat alias dalam SQL: $ SELECT last_name, COUNT(*) as quantity FROM student GROUP BY last_name ORDER BY COUNT(*) DESC ; Jadi saya berlebihan dengan Petrov - ternyata 16))

Tugas 8

Kondisi: Pilih 3 nama teratas yang paling sering diulang dari 'Siswa'. Urutkan berdasarkan kuantitas dalam urutan menurun. Seharusnya terlihat seperti ini:

nama	kuantitas
Alexander	27
Sergei	10
Petrus	7

Oh, untuk tujuan ini kita sudah memiliki Ivans, Peters dan Sidors. Oleh karena itu tidak perlu menambahkannya. Kita sudah tahu cara mengurutkannya. Satu-satunya hal yang tidak kami bicarakan hari ini adalah bagaimana memilih sejumlah rekaman tertentu. Ini telah muncul dalam solusi sebelumnya untuk masalah database. Bagi yang belum membacanya, bacalah. Selebihnya, langsung saja ke intinya: $ SELECT name, COUNT(*) as quantity FROM student GROUP BY name ORDER BY COUNT(*) DESC LIMIT 3; Seperti yang dapat dilihat dari kueri, jika Anda mengetahui urutan operator dalam kueri SELECT, tidak akan ada masalah dalam menjalankan kueri tersebut. Dan tugas ini masih ada di tangan kita. Dan ilmu yang dipaparkan sebelumnya cukup memadai untuk mengatasi masalah tersebut.

Tugas 9

Kondisi tugas: Pilih 'Siswa' yang memiliki jumlah 'Buku' terbanyak dan 'Guru' terkait. Urutkan berdasarkan kuantitas dalam urutan menurun. Seharusnya terlihat seperti ini:

Nama belakang guru	Nama belakang siswa	Kuantitas buku
Petrov	Sidorov	7
Ivanov	Smith	5
Petrov	Kankava	2

Jadi, tugas ini jelas lebih sulit dari tugas sebelumnya bukan? Pantas saja: baunya seperti gabung... dan lebih dari satu) Pertama, kita perlu memahami apa yang harus dilakukan... Jelas bahwa kuantitas Buku memerlukan pengelompokan. Tapi apa? Dan mengapa kita harus mengelompokkannya? Kueri melibatkan tiga tabel, pengelompokan, dan pengurutan. Dilihat dari fakta bahwa catatan di mana tidak ada buku tidak ditampilkan, itu berarti Anda perlu mengambil INNER JOIN. Kami juga akan membuat permintaan LEFT JOIN agar tidak ada masalah dengan ini. Dan ada beberapa pilihan. Hal pertama yang kita lakukan adalah menggabungkan tiga tabel menjadi satu record. Selanjutnya, kita mengelompokkan berdasarkan siswa dan menambahkan nama guru ke dalamnya. Apa yang akan kita pilih? Nama guru, siswa dan jumlah buku. Mari tambahkan data untuk permintaan tersebut:

• tiga guru;
• sepuluh buku;
• menghubungkan dua siswa dengan tiga guru.

Tiga guru

$ MASUKKAN KE guru(nama_belakang) NILAI ('Matvienko'); $ MASUKKAN KE guru(nama_belakang) NILAI ('Shevchenko'); $ MASUKKAN KE guru(nama_belakang) NILAI ('Vasilenko');

10 buku

Saya akan mengambil ID siswa 1 dan 2. Saya akan melampirkan buku pada mereka. Karena AUTO_INCREMENT tidak disetel, agar tidak menulis ID baru setiap saat, Anda perlu melakukan hal berikut: $ ALTER TABLE book MODIFY id INT NOT NULL AUTO_INCREMENT; Selanjutnya, tambahkan buku untuk siswa pertama: $ INSERT INTO book (title, student_id) VALUES('book1', 1); $ INSERT INTO buku (judul, student_id) VALUES('book2', 1); $ INSERT INTO buku (judul, student_id) VALUES('book3', 1); $ INSERT INTO buku (judul, student_id) VALUES('book4', 1); $ INSERT INTO buku (judul, student_id) VALUES('book5', 1); $ INSERT INTO buku (judul, student_id) VALUES('book6', 1); Dan buku untuk siswa kedua: $ INSERT INTO book (title, student_id) VALUES('book7', 2); $ INSERT INTO buku (judul, student_id) VALUES('book8', 2); $ INSERT INTO buku (judul, student_id) VALUES('book9', 2); $ INSERT INTO buku (judul, student_id) VALUES('book10', 2);

Koneksi guru-siswa

Untuk melakukan ini, tambahkan siswa_x_teachers ke tabel: $ INSERT INTO Students_x_teachers VALUES (1,1); $MASUKKAN KE NILAI siswa_x_guru(1,2); $MASUKKAN KE NILAI siswa_x_guru(2,3);

Mari kita laksanakan permintaan tersebut

Kita melakukan tahap pertama - kita menghubungkan tiga tabel menjadi satu record: $ SELECT * FROM teacher tch INNER JOIN Students_x_teachers st_x_tch ON tch.id = st_x_tch.teacher_id INNER JOIN student st ON st_x_tch.student_id = st.id INNER JOIN buku b ON st .id = b.id_siswa; Tentu saja, kami belum memiliki catatan apa pun, namun kami dapat melihat bahwa kami telah berhasil menghubungkan tiga tabel. Sekarang kita tambahkan pengelompokan buku, pengurutan dan field yang kita perlukan: $ SELECT tch.last_name, st.last_name, st.id, COUNT(*) as books FROM student st INNER JOIN book b ON st.id = b.student_id INNER GABUNG siswa_x_teachers st_x_tch ON st.id = st_x_tch.student_id INNER GABUNG guru tch ON tch.id = st_x_tch.teacher_id KELOMPOK OLEH st.id ORDER BY buku DESC; Tapi kita mendapatkan kesalahan dalam SQL dan jawaban berikut: Kode Kesalahan: 1055. Ekspresi #1 dari daftar SELECT tidak ada dalam klausa GROUP BY dan berisi kolom nonagregat 'final_task.tch.last_name' yang tidak bergantung secara fungsional pada kolom di GROUP BY klausa Tidak berhasil mengambil unsur-unsur ini karena ada hubungan banyak-ke-banyak antara guru dan siswa. Dan memang benar: kita tidak bisa hanya mendapatkan satu guru untuk setiap siswa. Jadi mari kita pergi ke arah lain. Mari kita gunakan sesuatu yang disebut View SQL. Apa idenya: kita membuat tampilan terpisah, yaitu tabel baru, sudah dengan pengelompokan yang kita butuhkan. Dan pada tabel ini kami akan menambahkan nama-nama guru yang diperlukan. Namun kami memperhitungkan fakta bahwa mungkin ada lebih dari satu guru, sehingga entri akan diulang. Buat tampilan: $ CREATE VIEW StudentBooks as SELECT st.last_name,st.id,COUNT(*) as books FROM student st INNER JOIN book b ON st.id=b.student_id GROUP BY st.id ORDER BY books DESC; Selanjutnya, kita bekerja dengan tampilan ini sebagai tabel sederhana yang memiliki tiga bidang: nama belakang siswa, id_siswa, dan jumlah buku. Berdasarkan ID siswa, kita juga dapat menambahkan guru melalui dua gabungan: $ SELECT tch.last_name as 'Teacher', sbw.last_name 'Student', sbw.books as 'Books' from studentbook sbw INNER JOIN Students_x_teachers stch ON sbw. id = stch.student_id INNER GABUNG guru tch ON tch.id = stch.teacher_id; Dan sekarang hasilnya adalah: Hah! Ini permintaan ya?) Ternyata seperti yang diharapkan: siswa dengan id=1 memiliki enam buku dan dua guru, dan siswa dengan id=2 memiliki empat buku dan satu guru.

Tugas 10

Syarat: Pilih 'Guru' yang memiliki jumlah 'Buku' terbanyak di antara semua 'Siswa' miliknya. Urutkan berdasarkan kuantitas dalam urutan menurun. Seharusnya terlihat seperti ini:

Nama belakang guru	Kuantitas buku
Petrov	9
Ivanov	5

Di sini kita dapat menggunakan permintaan yang sudah jadi dari tugas sebelumnya. Apa yang perlu kita ubah mengenai hal ini? Data ini sudah kita miliki, tinggal menambahkan pengelompokan lagi dan menghapus nama siswa dari data keluaran. Tapi pertama-tama, mari tambahkan satu siswa lagi ke gurunya agar hasilnya lebih menarik. Untuk melakukan ini, kita menulis: $ INSERT INTO Students_x_teachers VALUES (2, 1); Dan querynya sendiri: $ SELECT tch.last_name as 'Teacher', SUM(sbw.books) as 'Books' dari buku siswa sbw INNER JOIN Students_x_teachers stch ON sbw.id = stch.student_id INNER JOIN teacher tch ON tch.id = stch .teacher_id KELOMPOK BERDASARKAN tch.id; Hasilnya, kita mendapatkan: guru Vasilenko memiliki 10 buku, dan Shevchenko memiliki 6...)

Tugas 11

Kondisi: Pilih 'Guru' yang jumlah 'Buku' untuk semua 'Siswa' miliknya antara 7 dan 11. Urutkan berdasarkan kuantitas dalam urutan menurun. Seharusnya terlihat seperti ini:

Nama belakang guru	Kuantitas buku
Petrov	sebelas
Sidorov	9
Ivanov	7

Di sinilah kita akan menggunakan HAVING. Kami berbicara tentang dia. Permintaannya akan sama persis seperti sebelumnya, hanya Anda perlu menambahkan syarat jumlah buku dalam kisaran tertentu. Dan seperti yang saya katakan di artikel sebelumnya, ketika kita perlu melakukan pemfilteran selama pengelompokan dan/atau fungsi agregasi, kita perlu menggunakan HAVING : $ SELECT tch.last_name as 'Teacher', SUM(sbw.books) as 'Books' from buku siswa sbw INNER JOIN Students_x_teachers stch ON sbw.id = stch.student_id INNER JOIN teacher tch ON tch.id = stch.teacher_id KELOMPOK OLEH tch.id MEMILIKI SUM(sbw.books) > 6 DAN SUM(sbw.books) < 12; Saya telah menyorot bagian yang saya tambahkan. Dan sebenarnya, hasil yang diharapkan: Hanya Vasilenko yang lolos babak ini))

Tugas 12

Kondisi: Cetak semua 'nama_belakang' dan 'nama' semua 'Guru' dan 'Siswa' dengan kolom 'jenis' (siswa atau guru). Urutkan menurut abjad berdasarkan 'nama_belakang'. Seharusnya terlihat seperti ini:

nama keluarga	jenis
Ivanov	murid
Kankava	guru
Smith	murid
Sidorov	guru
Petrov	guru

Artinya, kita perlu menggabungkan dua keluaran, dan inilah tujuan UNION. Dengan kata lain, kita akan mengambil catatan dari siswa dan dari guru dan mencetak bersama-sama: $ SELECT last_name, 'teacher' as type from teacher UNION ALL select last_name, 'student' as type from student ORDER BY last_name; Dan akan ada guru dan siswa. Tampaknya semuanya sederhana, tetapi inilah saatnya kita melihat hasilnya. Jadi Anda perlu menebak dua hal.

Tugas 13

Kondisi: Tambahkan kolom 'nilai' ke tabel 'Siswa' yang ada, yang akan menyimpan mata kuliah yang diikuti siswa tersebut (nilai numerik dari 1 hingga 6). ALTER TABLE siswa ADD CONSTRAINT check_rate CHECK (rate > 0 AND rate < 7); Di sini kita menambahkan field melalui ALTER TABLE dan CHECK untuk menetapkan batas pada field ini dari 1 hingga 6.

Tugas 14

Kondisi: Item ini tidak wajib, tapi akan menjadi nilai tambah. Tulis fungsi yang akan menelusuri semua 'Buku' dan menampilkan semua 'judul' yang dipisahkan dengan koma. Di sini Anda hanya perlu mengembalikan satu baris sebagai hasil permintaan, yang akan berisi semua judul buku. Di sini sekali lagi saya harus mencari di Google. Ada fungsi seperti itu - GROUP_CONCAT , yang dapat dilakukan dengan sangat sederhana: $ SELECT GROUP_CONCAT(title) from book; Dan itu saja...)) Semua 14 tugas sudah siap.

kesimpulan

Uuhhh... Itu tidak mudah. Itu menarik. Tugasnya sepadan, saya lebih dari yakin. Saat kami melakukan tugas ini, kami membahas banyak hal yang sebelumnya tidak diketahui:

• TAMPILAN SQL
• GROUP_CONCAT
• PERSATUAN

dan seterusnya. Terima kasih kepada semua orang yang memiliki kekuatan untuk membaca dan mengulangi apa yang saya lakukan. Siapa yang tahu cara membuat permintaan lebih baik - tulis di komentar, saya pasti akan membacanya)

Daftar semua materi dalam seri ini ada di awal artikel ini.