Monday, July 30, 2012

Different Mysql Queries


 Retrieve the birthdate and address of the employee whose name is 'John B. Smith'.

Q0: SELECT            BDATE, ADDRESS
                FROM                      EMPLOYEE
                WHERE                   FNAME='John' AND MINIT='B’
                                                AND LNAME='Smith’;





Retrieve the name and address of all employees who work for the 'Research' department.
Q1: SELECT FNAME, LNAME, ADDRESS FROM EMPLOYEE JOIN DEPARTMENT ON EMPLOYEE.DNUMBER = DEPARTMENT.DNO WHERE DNAME='Research’;






 For every project located in 'Stafford', list the project number, the controlling department number, and the department manager's last name, address, and birthdate.
Q2: SELECT PNUMBER, DNUM, LNAME, BDATE, ADDRESS FROM PROJECT, DEPARTMENT, EMPLOYEE WHERE DNUM=DNUMBER AND MGRSSN=SSN AND PLOCATION=‘Stafford’;




For each employee, retrieve the employee's name, and the name of his or her immediate supervisor.
Q8: SELECT E.FNAME, E.LNAME, S.FNAME, S.LNAME FROM EMPLOYEE E S
WHERE   E.SUPERSSN=S.SSN;













Retrieve the SSN values for all employees.
Q9: SELECT SSN FROM EMPLOYEE

Retrieve ssn and dname values for all employee and department.
Q10: SELECT SSN, DNAME FROM EMPLOYEE, DEPARTMENT;









With the use of *, retrieve all the attribute values of the selected tuples.
Q1c: SELECT * FROM EMPLOYEE WHERE DNO=5;










SELECT * FROM EMPLOYEE JOIN DEPARTMENT ON DNO=DNUMBER WHERE DNAME='Research’ ;









For example, the result of Q11 may have duplicate SALARY values whereas Q11A does not have any duplicate values

Q11A: SELECT DISTINCT SALARY FROM EMPLOYEE;



Make a list of all project numbers for projects that involve an employee whose last name is 'Smith' as a worker or as a manager of the department that controls the project.
Q4: (SELECT PNAME FROM PROJECT, DEPARTMENT, EMPLOYEE WHERE DNUM=DNUMBER AND MGRSSN=SSN AND LNAME='Smith') UNION    (SELECT  PNAME FROM PROJECT, WORKS_ON, EMPLOYEE WHERE PNUMBER=PNO AND ESSN=SSN AND LNAME='Smith') ;













Retrieve the name and address of all employees who work for the 'Research' department.
QQ1. SELECT FNAME, LNAME, ADDRESS FROM EMPLOYEE WHERE DNO IN  (SELECT  DNUMBER FROM DEPARTMENT WHERE DNAME='Research' );













Retrieve the name of each employee who has a dependent with the same first name as the employee.

SELECT                  E.FNAME, E.LNAME FROM EMPLOYEE AS E WHERE E.SSN IN (SELECT ESSN FROM DEPENDENT WHERE ESSN=E.SSN AND E.FNAME=DEPENDENT_NAME)





Retrieve the names of employees who have no dependents.
SELECT                  FNAME, LNAME FROM EMPLOYEE WHERE     NOT EXISTS   (SELECT * FROM  DEPENDENT WHERE SSN=ESSN);












Retrieve the social security numbers of all employees who work on project number 1, 2, or 3.
SELECT                  DISTINCT ESSN FROM WORKS_ON WHERE PNO IN  (1, 2, 3) ;













These are different examples of JOIN.
QQ2. SELECT E.FNAME, E.LNAME, S.FNAME, S.LNAME FROM (EMPLOYEE E LEFT OUTER JOIN EMPLOYEE S   ON  E.SUPERSSN=S.SSN);















 SELECT FNAME, LNAME, ADDRESS FROM  (EMPLOYEE JOIN DEPARTMENT ON DNUMBER=DNO) WHERE       DNAME='Research’;










SELECT PNUMBER, DNUM,                LNAME, BDATE, ADDRESS FROM (PROJECT JOIN DEPARTMENT ON DNUM=DNUMBER) JOIN EMPLOYEE ON MGRSSN=SSN WHERE      PLOCATION='Stafford’






Find the maximum salary, the minimum salary, and the average salary among all employees.
SELECT                  MAX(SALARY), MIN(SALARY), AVG(SALARY) FROM EMPLOYEE;






 Find the maximum salary, the minimum salary, and the average salary among employees who work for the 'Research' department.
SELECT MAX(SALARY), MIN(SALARY),  AVG(SALARY) FROM     EMPLOYEE JOIN DEPARTMENT ON DNO=DNUMBER
WHERE   DNAME='Research‘;







Queries 17 and 18: Retrieve the total number of employees in the company (Q17), and the number of employees in the 'Research' department (Q18).
SELECT                  COUNT (*) FROM  EMPLOYEE;







For each department, retrieve the department number, the number of employees in the department, and their average salary.
Qq5. SELECT          DNO, COUNT (*), AVG (SALARY) FROM EMPLOYEE GROUP BY DNO;









For each project, retrieve the project number, project name, and the number of employees who work on that project.
SELECT PNUMBER, PNAME, COUNT(*) FROM                PROJECT JOIN WORKS_ON ON PNUMBER=PNO GROUP BY PNUMBER;












For each project on which more than two employees work , retrieve the project number, project name, and the number of employees who work on that project.
SELECT PNUMBER, PNAME, COUNT                (*) FROM                PROJECT, WORKS_ON WHERE PNUMBER=PNO GROUP BY PNUMBER, PNAME HAVING COUNT (*) > 2 ;










SELECT FNAME, LNAME, SALARY, 1.1*SALARY FROM EMPLOYEE, WORKS_ON, PROJECT WHERE SSN=ESSN AND PNO=PNUMBER AND PNAME='ProductX’;








Retrieve a list of employees and the projects each works in, ordered by the employee's department, and within each department ordered alphabetically by employee last name.
SELECT DNAME, LNAME, FNAME, PNAME FROM DEPARTMENT, EMPLOYEE, WORKS_ON, PROJECT WHERE DNUMBER=DNO AND SSN=ESSN AND        PNO=PNUMBER ORDER BY                DNAME, LNAME













Monday, July 16, 2012

Using Grant and Revoke to Manipulate the Permission Tables



Another way to set a password is to use a GRANT statement. Let me show you the syntax for granting all usage on a database to ‘richard’@’localhost’. The password for Richard is ‘father’, now with the use of grant, I will set the password to ‘chaplong’. I exited first, then I will log-in to my user account Richard with the old password ‘father’.



Now I will try to log-in my user account richard with the old password “father”. The image below it shows the error for logging in with the old password. When you have finished requesting for grant, now your password will change to a new one which is “chaplong”. 




The image below shows how to REVOKE a permission to inserting all tables in databases to regular user richard. It  will now cancelled all permissions regarding for manipulating the tables in all databases to regular user Richard.



Setting a Password for Mysql Account


This is the other way on how to change the user account password. By the use of SET PASSWORD statement, you will be able to change the password. You can set a password for the user you're connected as follows:
Set password=password(‘passpass’);
 See the image above, before I declare a new password, I used “password” as my password. Now, the current password is “passpass”. It overwrites the old password to a new one.
Take note: you have use this syntax to change password when you are currently logged in to account that you want to change the password.





I exited the user account with the old password “password”. Now I am going to log-in again but see the changes, the “password” before was “passpass” now.



Take note: You cannot set a new password when you are currently using the account that you want to change the password account.
 I have created new user account richard with the current password “father”. Next, I want to set new password for user richard. The password “father” will now set to “chaplong” as shown the above example.




Renaming Mysql User Account


The image above shows on how to change the old user name(richard) to new user name(santiago).
You have to follow the syntax: rename user ‘richard’@’localhost’ to ‘santiago’@’127.0.0.1’;
No changes have been made on the databases inside the user account, only the name of user account.



Dropping Mysql User Account

We have an example of dropping a user account  richard as shown above image.


Type the “drop user ‘richard’(user name)@’localhost; ”. After the dropping of user account, we can see if the user name “richard” is still on the list of mysql database.This shows that the user account richard have been successfully deleted. It’s not on the list.  All the tables or content are also deleted.







Creating Mysql User Account



Let me show you on how to create new user account to your mysql. First you need to connect to server, follow these simple steps:



D:
D:\>cd mysql\bin
    D:\>mysqld –u root
   Note: You need to set the path from which your mysql is located.





Log in to regular "user" account before you to be able to create new user account. 



The image above shows on how to create new user(richard) account with the account password “wordpass”.


This image shows the newly created user account on the third row on  the list of the database “mysql”.
















Take note:
You do not need to put semicolon after declaring your password. Example: mysql –u richard –pwordpass;  (You have encountered this prompt: “Error: Access denied to ‘richard’@’localhost’ using password(YES)).