SQL Queries for Student and Loan Data
Uploaded by
optimuz primeSQL Queries for Student and Loan Data
Uploaded by
optimuz primeCommon questions
Powered by AITo identify customers who have neither accounts nor loans, we utilize LEFT JOIN and check for NULLs. The query is: SELECT customer-name, customer-street, customer-city FROM customer LEFT JOIN depositor ON customer.customer-name = depositor.customer-name LEFT JOIN borrower ON customer.customer-name = borrower.customer-name WHERE depositor.customer-name IS NULL AND borrower.customer-name IS NULL. This checks for the absence of customer records in both depositor and borrower tables by looking for NULLs, thus effectively identifying the required customers .
To retrieve details of customers living in Dhaka owning accounts across all branches, we use a multi-join operation. The SQL is: SELECT DISTINCT customer-name, branch-name, branch-city FROM customer JOIN depositor ON customer.customer-name = depositor.customer-name JOIN account ON depositor.account-number = account.account-number WHERE customer-city = 'Dhaka' AND NOT EXISTS (SELECT 1 FROM branch WHERE branch.branch-name NOT IN (SELECT branch-name FROM account WHERE account.account-number = depositor.account-number)). This query identifies customers by verifying ownership of accounts in every branch's city .
To create a view isolating employees not working in any company, use: CREATE VIEW employee-no-company AS SELECT person-name, street, city FROM employee WHERE person-name NOT IN (SELECT person-name FROM works). This SQL command establishes a view filtering out employees present in the works relation, hence focusing only on those unemployed based on the existing schema .
To increase the salaries of teachers who taught 3-credit courses by 10%, an SQL UPDATE statement can be executed using a subquery to filter teachers based on the 'Teach' relation. The SQL statement would be: UPDATE Teacher SET salary = salary * 1.10 WHERE Tid IN (SELECT Tid FROM Teach JOIN Course ON Teach.course-id = Course.course-id WHERE credit-hour = 3). This query finds teachers who have taught any 3-credit courses and increases their salary by 10% .
The SQL expression to find the average, maximum, and minimum income of parents in Dhaka or Rajshahi with incomes above 500,000 involves using the GROUP BY and HAVING clauses. The query is: SELECT city, street, AVG(income), MAX(income), MIN(income) FROM Parents-S WHERE city IN ('Dhaka', 'Rajshahi') GROUP BY city, street HAVING AVG(income) > 500000. This groups the parent data by city and street, aggregating income values, and applying a condition on the average income .
To adjust account profits based on customer location, a conditional SQL UPDATE statement can be used: UPDATE account SET balance = CASE WHEN customer-city = 'Bhola' THEN balance * 1.10 WHEN customer-city = 'Dhaka' THEN balance * 1.05 ELSE balance * 1.06 END FROM account, customer, depositor WHERE account.account-number = depositor.account-number AND depositor.customer-name = customer.customer-name. This query selectively increases account balances based on the customer's city .
Linking employees to companies by salary and city involves a join query with conditions. Use: SELECT employee.person-name, employee.street, employee.city, company.name, company.city FROM employee INNER JOIN works ON employee.person-name = works.person-name INNER JOIN company ON works.company-name = company.name WHERE works.salary > 10000. This SQL command leverages inner joins connecting employee, works, and company relations by shared keys, filtered by salary .
Creating a role 'empl' with given privileges involves SQL statements: CREATE ROLE empl; GRANT INSERT, SELECT, DELETE ON employee TO empl; GRANT INSERT, SELECT, DELETE ON works TO empl; GRANT SELECT ON company TO empl. Assigning this role to users 'e1' and 'e2' is done using: GRANT empl TO e1, e2. This sequence creates a role 'empl' with specific privileges and assigns this role to specified users, leveraging SQL's role-based access control functionalities .
Managing loan updates for different borrowers involves a conditional SQL UPDATE command: UPDATE loan SET amount = CASE WHEN amount > 50000 THEN amount * 0.90 ELSE 0 END FROM borrower WHERE loan.loan-number = borrower.loan-number. This query decreases the amount by 10% for loans above 50,000 and sets others to zero, efficiently handling varying borrower conditions .
To convert loans from a specific branch such as 'NSU' to account entries, for data integrity, we need to ensure that loan numbers map correctly to account numbers and amounts to balances in the 'account' relation. The SQL statement is: INSERT INTO account (account number, branch name, balance) SELECT loan number, branch name, amount FROM loan WHERE branch-name = 'NSU'. This transfers the necessary loan details into the account relation effectively, maintaining the original data structure integrity .