Posts

Number of Days in Current Month

This Query to get number of days in Specific month at Specific Year : select to_number(to_char(last_day(to_date('01/02/2008','DD/Mm/yyyy')),'DD')) as days from dual you have to replace date '01/02/2008' with the date you want or the date from sysdate Thanks,

Manage Your Database Connection

This is an Example for how to manage a SQL Server database Connection using Visual Web Developer 2005 Dim _CON As String = "Data Source=MOHAMEDSAMI-PC\SQLEXPRESS;Initial Catalog=Test;Integrated Security=True" Here this is the Connection String and I will tell you in another blog how you can get this String easily, cause it is the most important thing, if you write the right connection you will have a successful connection to the database. Data Source = Your Database Instance, you can type it in many ways like (. /SQLEXPRESS, localhost/SQLEXPRESS, machine name/SQLEXPRESS). Initial Catalog = Your Database Name. Public newSqlConnection As SqlConnection = New SqlConnection(_CON) I defined a new Object from SqlConnection so I can manage and establish the Connection. I have to open the Connection so I can execute any Query I want. Private Function OpenConnection() As Boolean Try                newSqlConnection.Open()    ...

CURRENT_SCHEMA Approach

This method uses the CURRENT_SCHEMA session attribute to automatically point application users to the correct schema. First, we create the schema owner and an application user. CONN sys/password AS SYSDBA -- Remove existing users and roles with the same names. DROP USER schema_owner CASCADE; DROP USER app_user CASCADE; DROP ROLE schema_rw_role; DROP ROLE schema_ro_role; -- Schema owner. CREATE USER schema_owner IDENTIFIED BY password DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp QUOTA UNLIMITED ON users; GRANT CONNECT, CREATE TABLE TO schema_owner; -- Application user. CREATE USER app_user IDENTIFIED BY password DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp; GRANT CONNECT TO app_user; Notice that the application user can connect, but does not have any tablespace quotas or privileges to create objects. Next, we create some roles to allow read-write and read-only access. CREATE ROLE schema_rw_role; CREATE ROLE schema_ro_role; We want to give our application user read-wr...

Synonym Approach

This method relies on synonyms owned by the application user to point to the correct location of the schema objects. First, we create the users in a similar way to the previous example. CONN sys/password AS SYSDBA -- Remove existing users and roles with the same names. DROP USER schema_owner CASCADE; DROP USER app_user CASCADE; DROP ROLE schema_rw_role; DROP ROLE schema_ro_role; -- Schema owner. CREATE USER schema_owner IDENTIFIED BY password DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp QUOTA UNLIMITED ON users; GRANT CONNECT, CREATE TABLE TO schema_owner; -- Application user. CREATE USER app_user IDENTIFIED BY password DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp; GRANT CONNECT, CREATE SYNONYM TO app_user; Once again, the application user can connect, but does not have any tablespace quotas. The difference here is that the application user does have the privilege to create synonyms. Next, we create some roles to allow read-write and read-only access and grant the re...

Combine more than one Row in One Row

Working On Oracle Sample Database Tables ( Departments and Employees) 1- Suppose we want to select employees Names in each department Query : SELECT departments.department_id, departments.department_name, employees.first_name || ' ' || employees.last_name FROM employees JOIN departments ON departments.department_id = employees.department_id ORDER BY departments.department_id;   Results : 10 Administration Jennifer Whalen 20 Marketing Michael Hartstein 20 Marketing Pat Fay 30 Purchasing Den Raphaely 30 Purchasing Karen Colmenares 30 Purchasing Alexander Khoo 30 Purchasing Shelli Baida 30 Purchasing Sigal Tobias 30 Purchasing Guy Himuro 40 Human Resources Susan Mavris 50 Shipping Donald OConnell ................................................................. we want to know how we can make the results like : 10 Administration Jennifer Whalen 20 Marketing Michael Hartstein, Pat Fay 30 Purchasing Alexander Khoo, Den Raphaely, Guy Himuro, Karen Colmenares, Shelli Baid...

How to Select Random using Oracle

Working on Employees Table : Query : select * from (select * from Employees ORDER BY dbms_random.value ) where rownum <= 10 Results : 143 Randall Matos RMATOS 147 Alberto Errazuriz AERRAZUR 152 Peter Hall PHALL 135 Ki Gee KGEE 193 Britney Everett BEVERETT 122 Payam Kaufling PKAUFLIN 130 Mozhe Atkinson MATKINSO 181 Jean Fleaur JFLEAUR 110 John Chen JCHEN 105 David Austin DAUSTIN Every time you will run same Query you will have new results i wish this can help you in your Development Process Thanks,

SQL Interview Questions

You pick up the candidate's resume and it proudly proclaims "SQL Expert: 10 Years." Your boss trusts you, as the technical expert on the team, to participate briefly in the interview to gauge this individual's knowledge of SQL. Where to begin? I have asked literally hundreds of different questions during interviews over the past decade. Some were simple questions that were nevertheless remarkably effective ways of rating a candidate's comfort-level in SQL, whereas others just wasted precious interview time. Let me save you the latter! First I ask the candidate how they would personally rate their own understanding of SQL. I'm also curious what databases and tools they've used to write their SQL. This gives me a good idea where I should begin my questioning. Basic Questions Asking a question about joins is the common consensus for an opening question. More specifically, does the candidate know the different types of joins, or at least the differenc...