Users and books

-- کوئری اول تا سوم
SELECT
    books.name AS BookName,
    users.name,
    books.user_id
FROM
    books JOIN users ON books.user_id = users.id
WHERE
    /*users.id = 3; -- کوئری اول */
    
   /*users.id = 4 AND users.name = "amir"; -- کوئری دوم */
   
   /*books.name = "book1"; -- کوئری سوم */

-- کوئری چهارم
UPDATE books SET name="book6" WHERE id=6;

-- کوئری پنجم
DELETE FROM books WHERE user_id=5;

Unun..ali..nunu

U

Download DataBase

import sqlite3
conn = sqlite3.connect("library.db")
cursor = conn.cursor()
cursor.execute("""CREATE TABLE IF NOT EXISTS Users (id INTEGER PRIMARY KEY AUTOINCREMENT,name TEXT NOT NULL)""")
cursor.execute("""CREATE TABLE IF NOT EXISTS Books (id INTEGER PRIMARY KEY AUTOINCREMENT,name TEXT NOT NULL,user_id INTEGERFOREIGN KEY(user_id) REFERENCES Users(id))""")
cursor.execute("INSERT INTO Users (name) VALUES (?)", ("amir",))
cursor.execute("INSERT INTO Users (name) VALUES (?)", ("reza",))
cursor.execute("INSERT INTO Books (name, user_id) VALUES (?, ?)", ("book1", 1))
cursor.execute("INSERT INTO Books (name, user_id) VALUES (?, ?)", ("book2", 1))
cursor.execute("INSERT INTO Books (name, user_id) VALUES (?, ?)", ("book3", 2))
conn.commit()

def get_books_by_user_id(user_id):
    cursor.execute("SELECT * FROM Books WHERE user_id = ?", (user_id,))
    books = cursor.fetchall()
    return books

def get_books_by_name_and_user_id(name, user_id):
    cursor.execute("""SELECT b.*FROM Books bJOIN Users u ON u.id = b.user_idWHERE u.name = ? AND b.user_id = ?""", (name, user_id))
    books = cursor.fetchall()
    return books

def get_book_with_user(book_name):
    cursor.execute("""SELECT b.*, u.name AS user_name, u.id AS user_idFROM Books bJOIN Users u ON u.id = b.user_idWHERE b.name = ?""", (book_name,))
    book = cursor.fetchall()
    return book

def update_book_name_by_id(book_id, new_name):
    cursor.execute("UPDATE Books SET name = ? WHERE id = ?", (new_name, book_id))
    conn.commit()

def delete_books_by_user_id(user_id):
    cursor.execute("DELETE FROM Books WHERE user_id = ?", (user_id,))
    conn.commit()

print("کتاب‌های کاربری با شماره 3:")
books_user_3 = get_books_by_user_id(3)
print(books_user_3)

print("\nکتاب‌های کاربری با اسم 'amir' و شماره 4:")
books_amir_4 = get_books_by_name_and_user_id("amir", 4)
print(books_amir_4)

print("\nکتاب 'book1' به همراه مشخصات کاربر:")
book_with_user = get_book_with_user("book1")
print(book_with_user)

print("\nتغییر نام کتابی که شماره آن 6 است به 'book6'")
update_book_name_by_id(6, "book6")

print("\nحذف همه کتاب‌های کاربر شماره 5:")
delete_books_by_user_id(5)
conn.close()

Batman

B

Download Python

import sqlite3
# Connect to database (or to create it if there is no)
conn = sqlite3.connect('library.db')
cursor = conn.cursor()

# Create Tables (if needed)
cursor.execute('''
CREATE TABLE IF NOT EXISTS Users (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL
)
''')

cursor.execute('''
CREATE TABLE IF NOT EXISTS Books (
    id INTEGER PRIMARY KEY,
    user_id INTEGER,
    name TEXT NOT NULL,
    FOREIGN KEY (user_id) REFERENCES Users (id)
)
''')

# 1. نمایش کتاب‌های مربوط به کاربری با شماره 3
cursor.execute("SELECT * FROM Books WHERE user_id = 3;")
books_for_user_3 = cursor.fetchall()
print("کتاب‌های کاربری با شماره 3:", books_for_user_3)

# 2. نمایش کتاب‌های مربوط به کاربری با اسم "amir" و شماره 4
cursor.execute('''
SELECT b.* 
FROM Books b
JOIN Users u ON b.user_id = u.id
WHERE u.name = 'amir' AND u.id = 4;
''')
books_for_amir_4 = cursor.fetchall()
print("کتاب‌های کاربری با اسم 'amir' و شماره 4:", books_for_amir_4)

# 3. نمایش کتابی با اسم "book1" و مشخصات کاربر مربوط به آن
cursor.execute('''
SELECT b.*, u.*
FROM Books b
JOIN Users u ON b.user_id = u.id
WHERE b.name = 'book1';
''')
book1_details = cursor.fetchall()
print("کتاب 'book1' و مشخصات کاربر مربوط به آن:", book1_details)

# 4. تغییر اسم کتابی که شماره آن 6 است به "book6"
cursor.execute("UPDATE Books SET name = 'book6' WHERE id = 6;")
conn.commit()  # Save changes

# 5. حذف همه کتاب‌های کاربر شماره 5
cursor.execute("DELETE FROM Books WHERE user_id = 5;")
conn.commit()  # Save changes

# Close the connection to the database
conn.close()

Mma123

M

Download Python

--ساخت بدنه دیتابیس
create database book
create table users
(
id int constraint PK__users_id primary key ,
u_name nvarchar(50)
)
create table books
(
book_user_id int constraint FK__users_id foreign key references users(id),
id int constraint PK__books_id primary key ,
book_name nvarchar(50)
)
--1
select * from books where book_user_id = 3
--2
select b.id , b.book_name
from users u inner join books b on u.id = b.book_user_id and u.id = 4
group by b.id , b.book_name
--3
select u.id , u.u_name
from users u inner join books b on b.book_name = 'book1'
group by u.id , u.u_name
--4
update books
set book_name = 'book6'
where book_name = '6'
--5
delete from books
where book_user_id = 5

Sajad.karimi

S

Download DataBase